none
SQL Server 2K5 - INSERT INTO Script crash with no specific error

    Question

  • Hi,

    I need to run a sql statement that insert data into a table.

    I have about 14000 line to insert.

    In SQL Management studio, I did a script like that :

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....
    UNION ALL
    Select 'Values1','Values2','Value3',....
    UNION ALL
    Select 'Values1','Values2','Value3',....
    UNION ALL
    .
    .
    .

    When I try to insert around 1000 line and more, the script crash with no specific error code or message. I only see the message in the status bar saying, the script ended with error.

    Is there a way to avoid having that error ?

    Thanks


    Richard Martin Web Developer / BI Developer
    Thursday, September 16, 2010 4:49 PM

Answers

  • It is unclear what crashes, SQL or SSMS. SSMS is not good at large files.

    But there certainly is a problem with big SELECT UNION statements. It takes forever for SQL Server to compile them. The best is to take batches of some 50-100 INSERT SELECT UNION at a time. Or rather, that is the second best.

    The fastest way is to use INSERT EXEC as in this example:

    INSERT sometable (a, b, c)
       EXEC('SELECT 1, ''New York'', 234
             SELECT 2, ''London'', 923
             SELECT 3, ''Paris'', 1024
             SELECT 4, ''Munich'', 1980')

    It's more complicated to code, but compared to INSERT VALUES or INSERT SELECT with UNION it's like a jetplane.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Naomi NModerator Thursday, September 16, 2010 11:56 PM
    • Marked as answer by marric01 Friday, September 17, 2010 2:11 PM
    Thursday, September 16, 2010 10:25 PM

All replies

  • The maximum UNIONs is 8K I think on SQL Server 2008. If it fails at 1000, there might be some syntax error in your code.
    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 16, 2010 4:53 PM
  • The maximum UNIONs is 8K I think on SQL Server 2008. If it fails at 1000, there might be some syntax error in your code.
    Abdallah El-Chal, PMP, ITIL, MCTS

    As my post title say, i'm using SQL 2005. and when there syntax error, they are shown in SQl Management Studio ;-)
    Richard Martin Web Developer / BI Developer
    Thursday, September 16, 2010 5:01 PM
  • To try to avoid the error:

    ;with cte as (select 'Value1',... UNION ALL...)

    insert into ... select * from cte


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, September 16, 2010 5:10 PM
    Moderator
  • Can you post the exact error message? Also, when you receive an error like this in the status bar, you might need to scroll down in the Messages box to see the text for the error.

    As for the limit of UNIONs, I think this limit is the same on SQL 2005. Any reason you are doing 14000 UNIONs by the way?


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 16, 2010 5:10 PM
  • There is no such limit...... I inserted around 18,000 records with union all now....It executed correctly...

     

    it might be some other problem......  TRY...CATCH blocks will be helpful here??

     

    begin try

      -- your all union all statements..

    end try

    begin catch

    end catch

     

    Thursday, September 16, 2010 5:26 PM
  • To try to avoid the error:

    ;with cte as (select 'Value1',... UNION ALL...)

    insert into ... select * from cte


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Hi, I think that my problem is more related with memory issue in SQL because when I only run the Select .... UNION ALL SELECT .... UNION ALL ... it run like for 20 minutes and never seems to end.

    I think this is too much to handle for SQL

    I'm gone try to find another way to solve my problem

    Thanks


    Richard Martin Web Developer / BI Developer
    Thursday, September 16, 2010 5:29 PM
  • There is no such limit...... I inserted around 18,000 records now....It executed correctly...

     

    it might be some other problem......  TRY...CATCH blocks will be helpful here??

     

    begin try

      -- your all union all statements..

    end try

    begin catch

    end catch

     

    On which version? SQL Server 2005 sometimes gives error 8623 stating the following

    The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, September 16, 2010 5:31 PM
  • I'm using SQL 2008 R2 in both office and in my laptop......

    In mt work also, I used to insert Thousands of records with union all.... But as its sql2008 R2, it might didn't return any error..

     

    Not sure about 2005.........

    Thursday, September 16, 2010 5:34 PM
  • Instead you convert it into each select statement instead of union all.. it will finsh with in seconds...

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    Thursday, September 16, 2010 5:37 PM
  • Instead you convert it into each select statement instead of union all.. it will finsh with in seconds...

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....


    Hi,

    Yep, I just tried that and it give me an error about not having ennuff memory to run my script

    I have 14000 to insert in a table with 53 fields

    Rick


    Richard Martin Web Developer / BI Developer
    Thursday, September 16, 2010 5:54 PM
  • try adding a go between each insert statement as below

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO

    • Marked as answer by marric01 Thursday, September 16, 2010 7:48 PM
    • Unmarked as answer by marric01 Friday, September 17, 2010 2:11 PM
    Thursday, September 16, 2010 5:58 PM
  • try adding a go between each insert statement as below

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO

    INSERT INTO [TableName] (Field1,fireld2,field3,...)
    Select 'Values1','Values2','Value3',....

    GO


    Hi,

     

    Thanks SORNA

    Putting GO after each insert worked. It was clearly a memory problem.

     

    Thanks


    Richard Martin Web Developer / BI Developer
    Thursday, September 16, 2010 7:49 PM
  • It's a big difference between your original code and the new code. Right now you're doing insert one record at a time. Originally you tried to insert all of them in one transaction.

    Perhaps the better solution will be in between. Say, insert in batches of 1000 records at once.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, September 16, 2010 8:03 PM
    Moderator
  • It is unclear what crashes, SQL or SSMS. SSMS is not good at large files.

    But there certainly is a problem with big SELECT UNION statements. It takes forever for SQL Server to compile them. The best is to take batches of some 50-100 INSERT SELECT UNION at a time. Or rather, that is the second best.

    The fastest way is to use INSERT EXEC as in this example:

    INSERT sometable (a, b, c)
       EXEC('SELECT 1, ''New York'', 234
             SELECT 2, ''London'', 923
             SELECT 3, ''Paris'', 1024
             SELECT 4, ''Munich'', 1980')

    It's more complicated to code, but compared to INSERT VALUES or INSERT SELECT with UNION it's like a jetplane.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Naomi NModerator Thursday, September 16, 2010 11:56 PM
    • Marked as answer by marric01 Friday, September 17, 2010 2:11 PM
    Thursday, September 16, 2010 10:25 PM
  • Erland,

    Are you sure this is the best way? I also do have INSERT SELECT .. UNION ALL, so I'm going to switch to use your suggestion (although it will be more complex to write, I'm afraid as I will need to double all single quotes).

    Thanks again.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, September 16, 2010 11:58 PM
    Moderator
  • > Are you sure this is the best way? I also do have INSERT SELECT .. UNION ALL, so I'm going to switch to use your suggestion (although it will be more complex to write, I'm afraid as I will need to double all single quotes).

    Yes, I am very sure this is best way for performance (if you don't want to use XML).

    I have a more detailed discussion on http://www.sommarskog.se/arrays-in-sql-2005.html#listtoselect   You can also check the performance appendix for data, http://www.sommarskog.se/arrays-in-sql-perftest.html

    I should add that this is not something I thought of myself, but I got this tip from Jim Ebbers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Naomi NModerator Friday, September 17, 2010 12:53 PM
    • Marked as answer by marric01 Friday, September 17, 2010 2:11 PM
    • Unmarked as answer by marric01 Friday, September 17, 2010 2:11 PM
    Friday, September 17, 2010 7:54 AM
  • > Are you sure this is the best way? I also do have INSERT SELECT .. UNION ALL, so I'm going to switch to use your suggestion (although it will be more complex to write, I'm afraid as I will need to double all single quotes).

    Yes, I am very sure this is best way for performance (if you don't want to use XML).

    I have a more detailed discussion on http://www.sommarskog.se/arrays-in-sql-2005.html#listtoselect   You can also check the performance appendix for data, http://www.sommarskog.se/arrays-in-sql-perftest.html

    I should add that this is not something I thought of myself, but I got this tip from Jim Ebbers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

    I'm gone try this now and update you on the results ... thanks
    Richard Martin Web Developer / BI Developer
    Friday, September 17, 2010 1:53 PM
  • It is unclear what crashes, SQL or SSMS. SSMS is not good at large files.

    But there certainly is a problem with big SELECT UNION statements. It takes forever for SQL Server to compile them. The best is to take batches of some 50-100 INSERT SELECT UNION at a time. Or rather, that is the second best.

    The fastest way is to use INSERT EXEC as in this example:

    INSERT sometable (a, b, c)
    
      EXEC('SELECT 1, ''New York'', 234
    
         SELECT 2, ''London'', 923
    
         SELECT 3, ''Paris'', 1024
    
         SELECT 4, ''Munich'', 1980')

    It's more complicated to code, but compared to INSERT VALUES or INSERT SELECT with UNION it's like a jetplane.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


    Hi !!!

    Worked great for me. 1min to insert 15000 lines (53 field).

    But whats more important is the size of the .SQL file ... 8mb (exec way) compare to 26mb (with the insertinto select go ... insert into select go) way

    Thanks to everyone !


    Richard Martin Web Developer / BI Developer
    Friday, September 17, 2010 2:15 PM
  • Thanks, I've switched in one place in my code. Will do one more change in another place. Really great tip, although very tricky to handle the quotes.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, September 17, 2010 4:12 PM
    Moderator