Search code examples
sqlsql-serverimportbigdatasqlcmd

Import huge SQL file into SQL Server

I use the sqlcmd utility to import a 7 GB large SQL dump file into a remote SQL Server. The command I use is this:

sqlcmd -S IP address -U user -P password -t 0 -d database -i file.sql

After about 20-30 min the server regularly responds with:

Sqlcmd: Error: Scripting error.

Any pointers or advice?

Solution

  • I assume file.sql is just a bunch of INSERT statements. For a large amount of rows, I suggest using the BCP command-line utility. This will perform orders of magnitude faster than individual INSERT statements.

    You could also bulk insert data using the T-SQL BULK INSERT command. In that case, the file path needs to be accessible by the database server (i.e. UNC path or copied to a drive on the server) and along with needed permissions. See http://msdn.microsoft.com/en-us/library/ms188365.aspx.