user_192
0
I have a database table on a development server that is now fully populated after I set it running with an import routine for a CSV file containing 1.4 million rows.
I ran the Database Publishing Wizard on the table, and now I have a 286MB SQL script on my local machine. The problem is, I can't figure out how to run it. If I load it into SQL Server Management Studio Express I get an alert window that says "The operation could not be completed".
Any ideas on how I can get this SQL script to run?
use the sqlcmd tool to execute the file..
sqlcmd -S myServer\instanceName -i C:\myScript.sql
In case your have an unexplained "script error" for large sql files (> 100MB) which includes several INSERT, just replace "INSERT INTO" by "GO INSERT INTO" in your file, which will reduce size of transaction.
Yes we could do that, I tried with BCP(Bulk Copy Program) approach in order to avoid OutOfMemory issue.
Note : Tried in SQLServer 2014
In BCP, first we need to export the Source DataBase data to bcp file(in local directory folder) and then need to import that bcp file to Source DataBase
Below are the cake walk steps:
Note:
a) Make sure empty table is present in Destination DataBase
b) Make sure Temp folder is present in C drive
1) Create a bat file named as Export_Data.bat with below command
bcp.exe [Source_DataBase_Name].[dbo].[TableName] OUT "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q
pause
2) Run that bat file, as a result of that a bcp file will get generated in Temp folder
3) Then Create a another bat file named as Import_Data.bat with below command
bcp.exe [Destination_DataBase_Name].[dbo].[TableName] IN "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q
Pause
And here we go!!
Running something that large inside a single transaction is not a good idea. Therefore, I'd recommend breaking up the file into smaller, more manageable chunks.
Another option is to look at some of the other ways to import CSV data directly.