Batch wise insert in table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Batch wise insert in table

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2014-03-12 : 08:24:29

I have large table and need to move data regularly weekend.

I need batch insert that how can I do.. I tried below code and it goes in loop continuously. PlEASE SUGGEST better fast way.


-- CREATE TABLE tmptbl1 (ids int)
--------------------------------------
;WITH q (n) AS (
   SELECT 1
   UNION ALL
   SELECT n + 1
   FROM   q
   WHERE  n < 10000
)
INSERT INTO tmptbl1 
SELECT * FROM q
OPTION (MAXRECURSION 0)
--------------------------------------
-- CREATE TABLE tmptbl2 (ids int)

WHILE(1 = 1)
BEGIN
    INSERT INTO tmptbl2 (ids)
    SELECT TOP 1000 ids FROM tmptbl1
    IF (@@ROWCOUNT = 0)
        BREAK;
END

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-12 : 10:11:37

that's because of the select statement. The logic of your code is such that it picks top 1000 records from the table and insert it into table2. next time it again do the same. Ultimately, @@rowCount will always be > 0

There should be a check to ensure that the record already added do not add again. e.g.

Insert into tmptbl2 (ids)
select top 1000 ids
from tmpTbl1 A
Left Join tmptbl2 on tmptbl1.Ids=tmptbl2.ids
where tmptbl2.ids IS NULL

Cheers
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-13 : 18:44:37

quote:
Originally posted by keyursoni85

PlEASE SUGGEST better fast way.

Use SSIS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources