Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
why is my insert-select failing?

Answered why is my insert-select failing?

  • Wednesday, September 19, 2012 6:04 AM
     
     
    Sign In to Vote
    0

    I am running the following SQL -

    insert into table1 select * from table2

    The query fails. Table2 has 60+ million rows. It's about 4GB of data in a database with over 100GB free.

    Am I reaching some limit ?

    Thanks !

     

All Replies

  • Wednesday, September 19, 2012 6:17 AM
     
     
    Sign In to Vote
    0

    The query fails.

    Hello,

    And with which error message does it fail (we can't guess that)?

    Olaf Helper

    Blog Xing

     
  • Wednesday, September 19, 2012 12:56 PM
     
     
    Sign In to Vote
    0

    Hi Olaf - this is exactly the problem. There really isn't a very descriptive message.

    Running it on the Azure web interface just comes back with "query failed".

    Running it on Sql Server Management Studio 2012 shows the timer running up to close to 30 minutes - and then I get this message -

    Msg 10054, Level 20, State 0, Line 0

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

    When I initially uploaded the 60 million rows I used the Import/Export Wizard which took hours to do so - but didn't cut off at 30 minutes - so I doubt there is a fundamental problem with my network provider. Yet an insert of this data into another table fails after 30 minutes.

    Why should it take > 30 minutes in the first place to copy a few GB of data within the database? That's not a very fast copy speed. How do we improve it?

    I also tried the SELECT INTO approach and got the following message. Why is it not supported?

    Msg 40510, Level 16, State 1, Line 1

    Statement 'SELECT INTO' is not supported in this version of SQL Server.

    Thank you very much !

     
  • Wednesday, September 19, 2012 3:44 PM
     
     Answered
    Sign In to Vote
    1

    Running it on the Azure web interface just comes back with "query failed".
    ...
    Msg 40510, Level 16, State 1, Line 1
    Statement 'SELECT INTO' is not supported in this version of SQL Server.

    So you are using SQL Azure database as destination; you didn't mentioned it before?

    The connection closing could cause by the SQL Azure Throttling, see MSDN Data Migration to Windows Azure SQL Database: Tools and Techniques => "Throttling" and also "Transfer Large Data to SQL Database"

    Additional: http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management-en-us.aspx

    See also: Federation Guidelines and Limitations

    Olaf Helper

    Blog Xing