How do you import a large MS SQL sql file

Question

I use RedGate SQL data compare and generated a  sql file  so I could run it on my local machine  But the problem is that the file is over 300mb  which means I can t do copy and paste because the clipboard won t be able to handle it  and when I try to open the file in SQL Server Management Studio I get an error about the file being too large   Is there a way to run a large  sql file  The file basically contains data for two new tables

User · Answer

Run it at the command line with osql  see here   http   metrix fcny org wiki display dev How to execute a  SQL script using OSQL

User · Answer

You can use this tool as well  It is really useful   BigSqlRunner

User · Answer

Run it at the command line with osql  see here   http   metrix fcny org wiki display dev How to execute a  SQL script using OSQL

User · Answer

I am using MSSQL Express 2014 and none of the solutions worked for me  They all just crashed SQL  As I only needed to run a one off script with many simple insert statements I got around it by writing a little console app as a very last resort   class Program       static void Main string   args                RunScript               private static void RunScript                 My DataEntities db   new My DataEntities             string line           System IO StreamReader file              new System IO StreamReader  c   ukpostcodesmssql sql            while   line   file ReadLine       null                        db Database ExecuteSqlCommand line                      file Close

User · Answer

Your question is quite similar to this one  You can save your file script as  txt or  sql and run it from Sql Server Management Studio  I think the menu is Open Query  then just run the query in the SSMS interface   You migh have to update the first line  indicating the database to be created or selected on your local machine   If you have to do this data transfer very often  you could then go for replication  Depending on your needs  snapshot replication could be ok  If you have to synch the data between your two servers  you could go for a more complex model such as merge replication     EDIT  I didn t notice that you had problems with SSMS linked to file size  Then you can go for command-line  as proposed by others  snapshot replication  publish on your main server  subscribe on your local one  replicate  then unsubscribe  or even backup restore

User · Answer

From the command prompt  start up sqlcmd   sqlcmd -S  lt server gt  -i C   lt your file here gt  sql    Just replace  lt server gt  with the location of your SQL box and  lt your file here gt  with the name of your script   Don t forget  if you re using a SQL instance the syntax is   sqlcmd -S  lt server gt  instance    Here is the list of all arguments you can pass sqlcmd   Sqlcmd             -U login id            -P password     -S server              -H hostname            -E trusted connection     -d use database name   -l login timeout       -t query timeout      -h headers             -s colseparator        -w screen width     -a packetsize          -e echo input          -I Enable Quoted Identifiers     -c cmdend              -L c  list servers clean output      -q  cmdline query      -Q  cmdline query  and exit      -m errorlevel          -V severitylevel       -W remove trailing spaces     -u unicode output      -r 0 1  msgs to stderr     -i inputfile           -o outputfile          -z new password     -f    i   o     -Z new password and exit      -k 1 2  remove replace  control characters     -y variable length type display width     -Y fixed length type display width     -p 1  print statistics colon format      -R use client regional setting     -b On error batch abort     -v var    value        -A dedicated admin connection     -X 1  disable commands  startup script  environment variables  and exit      -x disable variable substitution     -  show syntax summary

User · Answer

I have encountered the same problem and invested a whole day to find the way out but this is resolved by making the copy of  sql file and change the extension to  txt file and open the  txt file into chrome browser  I have seen the magic and file is opened in a browser   Thanks and Best Regards

User · Answer

Take command prompt with administrator privilege  Change directory to where the  sql file stored  Execute the following command sqlcmd -S  your server name  -U  user name of server  -P  password of server  -d      db name -i script sql

User · Answer

Run it at the command line with osql  see here   http   metrix fcny org wiki display dev How to execute a  SQL script using OSQL

User · Answer

Hope this help you   sqlcmd -u UserName -s  lt ServerName InstanceName gt  -i U   lt Path gt  script sql

User · Answer

I had exactly the same issue and had been struggling for a while then finally found the solution which is to set -a parameter to the sqlcmd in order to change its default packet size   sqlcmd -S  servername  -d  databasename  -i  scriptfilename  -a 32767

User · Answer

You can use this tool as well  It is really useful   BigSqlRunner

User · Answer

I had similar problem  My file with sql script was over 150MB of size  with almost 900k of very simple INSERTs   I used solution advised by Takuro  as the answer in this question  but I still got error with message saying that there was not enough memory   There is insufficient system memory in resource pool  internal  to run this query     What helped me was that I put GO command after every 50k INSERTs    It s not directly addressing the question  file size  but I believe it resolves problem that is indirectly connected with large size of sql script itself  In my case many insert commands

User · Answer

From the command prompt  start up sqlcmd   sqlcmd -S  lt server gt  -i C   lt your file here gt  sql    Just replace  lt server gt  with the location of your SQL box and  lt your file here gt  with the name of your script   Don t forget  if you re using a SQL instance the syntax is   sqlcmd -S  lt server gt  instance    Here is the list of all arguments you can pass sqlcmd   Sqlcmd             -U login id            -P password     -S server              -H hostname            -E trusted connection     -d use database name   -l login timeout       -t query timeout      -h headers             -s colseparator        -w screen width     -a packetsize          -e echo input          -I Enable Quoted Identifiers     -c cmdend              -L c  list servers clean output      -q  cmdline query      -Q  cmdline query  and exit      -m errorlevel          -V severitylevel       -W remove trailing spaces     -u unicode output      -r 0 1  msgs to stderr     -i inputfile           -o outputfile          -z new password     -f    i   o     -Z new password and exit      -k 1 2  remove replace  control characters     -y variable length type display width     -Y fixed length type display width     -p 1  print statistics colon format      -R use client regional setting     -b On error batch abort     -v var    value        -A dedicated admin connection     -X 1  disable commands  startup script  environment variables  and exit      -x disable variable substitution     -  show syntax summary

User · Answer

Your question is quite similar to this one  You can save your file script as  txt or  sql and run it from Sql Server Management Studio  I think the menu is Open Query  then just run the query in the SSMS interface   You migh have to update the first line  indicating the database to be created or selected on your local machine   If you have to do this data transfer very often  you could then go for replication  Depending on your needs  snapshot replication could be ok  If you have to synch the data between your two servers  you could go for a more complex model such as merge replication     EDIT  I didn t notice that you had problems with SSMS linked to file size  Then you can go for command-line  as proposed by others  snapshot replication  publish on your main server  subscribe on your local one  replicate  then unsubscribe  or even backup restore

User · Answer

Take command prompt with administrator privilege  Change directory to where the  sql file stored  Execute the following command sqlcmd -S  your server name  -U  user name of server  -P  password of server  -d      db name -i script sql

User · Answer

I had exactly the same issue and had been struggling for a while then finally found the solution which is to set -a parameter to the sqlcmd in order to change its default packet size   sqlcmd -S  servername  -d  databasename  -i  scriptfilename  -a 32767

User · Answer

Run it at the command line with osql  see here   http   metrix fcny org wiki display dev How to execute a  SQL script using OSQL

User · Answer

Your question is quite similar to this one  You can save your file script as  txt or  sql and run it from Sql Server Management Studio  I think the menu is Open Query  then just run the query in the SSMS interface   You migh have to update the first line  indicating the database to be created or selected on your local machine   If you have to do this data transfer very often  you could then go for replication  Depending on your needs  snapshot replication could be ok  If you have to synch the data between your two servers  you could go for a more complex model such as merge replication     EDIT  I didn t notice that you had problems with SSMS linked to file size  Then you can go for command-line  as proposed by others  snapshot replication  publish on your main server  subscribe on your local one  replicate  then unsubscribe  or even backup restore

User · Answer

I had similar problem  My file with sql script was over 150MB of size  with almost 900k of very simple INSERTs   I used solution advised by Takuro  as the answer in this question  but I still got error with message saying that there was not enough memory   There is insufficient system memory in resource pool  internal  to run this query     What helped me was that I put GO command after every 50k INSERTs    It s not directly addressing the question  file size  but I believe it resolves problem that is indirectly connected with large size of sql script itself  In my case many insert commands

User · Answer

Hope this help you   sqlcmd -u UserName -s  lt ServerName InstanceName gt  -i U   lt Path gt  script sql

User · Answer

Your question is quite similar to this one  You can save your file script as  txt or  sql and run it from Sql Server Management Studio  I think the menu is Open Query  then just run the query in the SSMS interface   You migh have to update the first line  indicating the database to be created or selected on your local machine   If you have to do this data transfer very often  you could then go for replication  Depending on your needs  snapshot replication could be ok  If you have to synch the data between your two servers  you could go for a more complex model such as merge replication     EDIT  I didn t notice that you had problems with SSMS linked to file size  Then you can go for command-line  as proposed by others  snapshot replication  publish on your main server  subscribe on your local one  replicate  then unsubscribe  or even backup restore

User · Answer

I am using MSSQL Express 2014 and none of the solutions worked for me  They all just crashed SQL  As I only needed to run a one off script with many simple insert statements I got around it by writing a little console app as a very last resort   class Program       static void Main string   args                RunScript               private static void RunScript                 My DataEntities db   new My DataEntities             string line           System IO StreamReader file              new System IO StreamReader  c   ukpostcodesmssql sql            while   line   file ReadLine       null                        db Database ExecuteSqlCommand line                      file Close

User · Answer

The file basically contain data for two new tables    Then you may find it simpler to just DTS  or SSIS  if this is SQL Server 2005   the data over  if the two servers are on the same network   If the two servers are not on the same network  you can backup the source database and restore it to a new database on the destination server   Then you can use DTS SSIS  or even a simple INSERT INTO SELECT  to transfer the two tables to the destination database

User · Answer

From the command prompt  start up sqlcmd   sqlcmd -S  lt server gt  -i C   lt your file here gt  sql    Just replace  lt server gt  with the location of your SQL box and  lt your file here gt  with the name of your script   Don t forget  if you re using a SQL instance the syntax is   sqlcmd -S  lt server gt  instance    Here is the list of all arguments you can pass sqlcmd   Sqlcmd             -U login id            -P password     -S server              -H hostname            -E trusted connection     -d use database name   -l login timeout       -t query timeout      -h headers             -s colseparator        -w screen width     -a packetsize          -e echo input          -I Enable Quoted Identifiers     -c cmdend              -L c  list servers clean output      -q  cmdline query      -Q  cmdline query  and exit      -m errorlevel          -V severitylevel       -W remove trailing spaces     -u unicode output      -r 0 1  msgs to stderr     -i inputfile           -o outputfile          -z new password     -f    i   o     -Z new password and exit      -k 1 2  remove replace  control characters     -y variable length type display width     -Y fixed length type display width     -p 1  print statistics colon format      -R use client regional setting     -b On error batch abort     -v var    value        -A dedicated admin connection     -X 1  disable commands  startup script  environment variables  and exit      -x disable variable substitution     -  show syntax summary

User · Answer

I have encountered the same problem and invested a whole day to find the way out but this is resolved by making the copy of  sql file and change the extension to  txt file and open the  txt file into chrome browser  I have seen the magic and file is opened in a browser   Thanks and Best Regards

User · Answer

The file basically contain data for two new tables    Then you may find it simpler to just DTS  or SSIS  if this is SQL Server 2005   the data over  if the two servers are on the same network   If the two servers are not on the same network  you can backup the source database and restore it to a new database on the destination server   Then you can use DTS SSIS  or even a simple INSERT INTO SELECT  to transfer the two tables to the destination database

User · Answer

From the command prompt  start up sqlcmd   sqlcmd -S  lt server gt  -i C   lt your file here gt  sql    Just replace  lt server gt  with the location of your SQL box and  lt your file here gt  with the name of your script   Don t forget  if you re using a SQL instance the syntax is   sqlcmd -S  lt server gt  instance    Here is the list of all arguments you can pass sqlcmd   Sqlcmd             -U login id            -P password     -S server              -H hostname            -E trusted connection     -d use database name   -l login timeout       -t query timeout      -h headers             -s colseparator        -w screen width     -a packetsize          -e echo input          -I Enable Quoted Identifiers     -c cmdend              -L c  list servers clean output      -q  cmdline query      -Q  cmdline query  and exit      -m errorlevel          -V severitylevel       -W remove trailing spaces     -u unicode output      -r 0 1  msgs to stderr     -i inputfile           -o outputfile          -z new password     -f    i   o     -Z new password and exit      -k 1 2  remove replace  control characters     -y variable length type display width     -Y fixed length type display width     -p 1  print statistics colon format      -R use client regional setting     -b On error batch abort     -v var    value        -A dedicated admin connection     -X 1  disable commands  startup script  environment variables  and exit      -x disable variable substitution     -  show syntax summary

User · Answer

The file basically contain data for two new tables    Then you may find it simpler to just DTS  or SSIS  if this is SQL Server 2005   the data over  if the two servers are on the same network   If the two servers are not on the same network  you can backup the source database and restore it to a new database on the destination server   Then you can use DTS SSIS  or even a simple INSERT INTO SELECT  to transfer the two tables to the destination database

User · Answer

The file basically contain data for two new tables    Then you may find it simpler to just DTS  or SSIS  if this is SQL Server 2005   the data over  if the two servers are on the same network   If the two servers are not on the same network  you can backup the source database and restore it to a new database on the destination server   Then you can use DTS SSIS  or even a simple INSERT INTO SELECT  to transfer the two tables to the destination database

[sql] How do you import a large MS SQL .sql file?

Examples related to sql

Examples related to sql-server

Examples related to import