locked
Bulk insert of Data from one table to another table RRS feed

  • Question

  • Hi,

    I have one table (TABLE A) which updates with milions of records every day. Now Every day we clear the data of this TABLE A; but before clear the data from TABLE A we get all data from TABLE A and move it into another table TABLE B due to some purpose and we are using 

    INSERT INTO TABLE B (col1,col2,.......colN) values (SELECT col1,col2,..........colN from TABLE A)

    This approch is not giving us a best performance as we are selecting all data from TABLE A and then inserting into TABLE B in one shot and i have millions of rows in my TABLE A (sometimes 16 millions).

    Can anyone suggest any other approch to do so that i can get best performance ?

    --Jai


    JP Sharma
    Thursday, October 21, 2010 6:07 PM

Answers

  • Can't you do SELECT * INTO tableB FROM tableA?
    • Marked as answer by JP S Friday, October 22, 2010 3:01 PM
    Thursday, October 21, 2010 7:16 PM

All replies

  • Can't you do SELECT * INTO tableB FROM tableA?
    • Marked as answer by JP S Friday, October 22, 2010 3:01 PM
    Thursday, October 21, 2010 7:16 PM
  • Before we go any further, which version of SQL Server do you use?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, October 21, 2010 10:24 PM
  • How are you clearing the table? You may want to do your insert in batches or even better in SSIS where you can do multiple inserts in parallel. I suggest exploring SSIS for this task.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, October 21, 2010 10:29 PM
  • Can you tell me the difference that How does this SELECT * INTO tableB FROM tableA and INSERT INTO TABLE B (col1,col2,.......colN) values (SELECT col1,col2,..........colN from TABLE A) works internally in t-sql ? Or do you have any resouces where i can find in detail about both t-sql ststaements ? Thanks in advance.

     

    --Jai

     


    JP Sharma
    Friday, October 22, 2010 3:04 PM
  • > Can you tell me the difference that How does this SELECT * INTO tableB FROM tableA* and*INSERT INTO TABLE B (col1,col2,.......colN) values (SELECT col1,col2,..........colN from TABLE A) works internally in t-sql ? Or do you have any resouces where i can find in detail about both t-sql ststaements ? Thanks in advance.

    Again, please tell us which version of SQL Server you are using. And, yes, that matters for your question.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, October 22, 2010 9:57 PM
  • http://msdn.microsoft.com/en-us/library/ms188029.aspx check under logging behaviour, please browse through books online for other differences

     

    Wednesday, September 28, 2011 1:18 PM