Batch Insert in sql server [Resolved]

Posted by Murugavelmsc under Sql Server on 9/26/2013 | Points: 10 | Views : 1752 | Status : [Member] | Replies : 3
Hi Expert,

I need to insert batch wise records to the table use wait for delay

For eg.
I have 1000 records to be insert
for every 10 mins to insert the record.

Please help me.

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Sample Script to insert Batch-wise records

--In following sample, I specified 2 as the batch size. You can change it to 1000 in your case. Please see:

DECLARE @Table1 TABLE (Col1 INT)
DECLARE @Table2 TABLE (Col1 INT)

INSERT INTO @Table1 VALUES (1)
INSERT INTO @Table1 VALUES (3)
INSERT INTO @Table1 VALUES (5)
INSERT INTO @Table1 VALUES (6)
INSERT INTO @Table1 VALUES (7)
INSERT INTO @Table1 VALUES (8)
INSERT INTO @Table1 VALUES (9)

DECLARE @Count INT
DECLARE @StartNum INT

SET @StartNum = 1

SELECT @Count = COUNT(*) FROM @Table1

WHILE @StartNum<=@Count
BEGIN
WITH cte
AS
(
SELECT Col1,ROW_NUMBER() OVER (ORDER BY Col1) AS 'RN' FROM @Table1
)

INSERT INTO @Table2 SELECT Col1 FROM cte WHERE RN >= @StartNum AND RN < @StartNum+2

SET @StartNum += 2

WAITFOR DELAY '00:00:01'
END

SELECT * FROM @table1

SELECT * FROM @table2


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Why you want to use WAITFOR DELAY command....

Examples of WAITFOR
----Delay for 20 seconds
WAITFOR DELAY '000:00:20'
SELECT '20 Second Delay'
GO
----Delay till 7 AM
WAITFOR TIME '7:00:00'
SELECT 'Good Morning'
GO



Can you tell us where you want to use this kind of approach?

We will provide alternates if possible

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you have millions of records better to use SSIS For EACH Loop Task for the Batch-wise Insert operation
Refer this link for an idea
http://www.sqlservercentral.com/Forums/Topic910173-147-1.aspx

"Mark As Answer"

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response