December 4, 2009 at 6:16 am
I want to insert 1 million records into a table. And for every 100 inserts, i want to commit transaction. Can someone please modify the code below to work. ( @colm1 is an integer column with primary key)
CREATE PROCEDURE InsertRows_MT
AS
BEGIN
DECLARE @colm1 int
DECLARE @intFlag int
DECLARE @date2 DATETIME
SET @date2 = '2009-04-12 00:00:00'
SET @colm1 = 1
SET @intFlag = 1
WHILE (@colm1 <=1000000)
BEGIN TRAN
INSERT INTO Table1 VALUES(@colm1, 'aaaaaa',@date2,'bbbbbb','4367.376','zzzzz')
SET @colm1 = @colm1 + 1
SET @intFlag = @intFlag + 1
IF @intFlag = 100 THEN
COMMIT TRAN
SET @intFlag =1
END
END
END
December 4, 2009 at 6:26 am
Chandu-212374 (12/4/2009)
Can someone please modify the code below to work.
What's it not doing, what error is it throwing? Basically, what's wrong with that code that you want help in fixing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2009 at 6:45 am
Msg 102, Level 15, State 1, Procedure InsertRows_MT, Line 29
Incorrect syntax near 'END'.
The editor is pointing the error at the last 'END' statement.
December 4, 2009 at 6:52 am
Chandu-212374 (12/4/2009)
Msg 102, Level 15, State 1, Procedure InsertRows_MT, Line 29Incorrect syntax near 'END'.
The editor is pointing the error at the last 'END' statement.
How to post data/code By Jeff Moden[/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 4, 2009 at 7:14 am
You shouldn't need the THEN in the IF, I think you had an extra END in there, also. This appears to be working:
CREATE TABLE Table1 (col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50), col5 varchar(50), col6 varchar(50))
DECLARE @colm1 int
DECLARE @intFlag int
DECLARE @date2 DATETIME
SET @date2 = '2009-04-12 00:00:00'
SET @colm1 = 1
SET @intFlag = 1
WHILE (@colm1 <=1000000)
BEGIN
BEGIN TRAN
INSERT INTO Table1 VALUES(@colm1, 'aaaaaa','123','123','123','zzzzz')
SET @colm1 = @colm1 + 1
SET @intFlag = @intFlag + 1
IF (@intFlag = 100)
COMMIT TRAN
SET @intFlag =1
END
December 4, 2009 at 7:29 am
Please bear with me as I'm a bit new to t-sql, but instead of
CREATE PROCEDURE InsertRows_MT
AS
BEGIN
DECLARE @colm1 int
DECLARE @intFlag int
DECLARE @date2 DATETIME
SET @date2 = '2009-04-12 00:00:00'
SET @colm1 = 1
SET @intFlag = 1
WHILE (@colm1 <=1000000)
BEGIN TRAN
INSERT INTO Table1 VALUES(@colm1, 'aaaaaa',@date2,'bbbbbb','4367.376','zzzzz')
SET @colm1 = @colm1 + 1
SET @intFlag = @intFlag + 1
IF @intFlag = 100 THEN
COMMIT TRAN
SET @intFlag =1
END
END
END
Shouldn't it be
CREATE PROCEDURE InsertRows_MT
AS
BEGIN
DECLARE @colm1 int
DECLARE @intFlag int
DECLARE @date2 DATETIME
SET @date2 = '2009-04-12 00:00:00'
SET @colm1 = 1
SET @intFlag = 1
WHILE (@colm1 <=1000000)
BEGIN TRAN
INSERT INTO Table1 VALUES(@colm1, 'aaaaaa',@date2,'bbbbbb','4367.376','zzzzz')
SET @colm1 = @colm1 + 1
SET @intFlag = @intFlag + 1
IF @intFlag = 100
BEGIN
COMMIT TRAN
SET @intFlag =1
END
END
END
December 4, 2009 at 7:37 am
I spoke too soon - maybe it should be this instead:
CREATE PROCEDURE InsertRows_MT
AS
BEGIN
DECLARE @colm1 int
DECLARE @intFlag int
DECLARE @date2 DATETIME
SET @date2 = '2009-04-12 00:00:00'
SET @colm1 = 1
SET @intFlag = 0
WHILE (@colm1 <=1000000)
BEGIN TRAN
INSERT INTO Table1 VALUES(@colm1, 'aaaaaa',@date2,'bbbbbb','4367.376','zzzzz')
SET @colm1 = @colm1 + 1
SET @intFlag = @intFlag + 1
IF @intFlag = 100
BEGIN
COMMIT TRAN
SET @intFlag = 0
END
END
END
You need to init @intFlag to 0 instead of 1, otherwise you're going to commit on every 99th insert, and the last transaction won't commit, because it won't be an even 100.
December 4, 2009 at 7:55 am
Where is the data coming from that you are trying to insert? I seem to missing something when I look at the code.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply