Batch Insert in sql server [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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/

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

Latest Posts