Click here to Skip to main content
14,367,095 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi frnds...

I want to know how to autogenerate a continuous number and insert the number in table using execute sql task...

http://www.sql-server-performance.com/2008/ssis-auto-increment/[]

From the above url i learned to autogenerate a number using script component task but i want to know to use the above number in insert query...
for example i need to generate a number starting from 000001 and increment continuously for each row and insert the number into insert query via execute sql task....


Thanks
Nithya.
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

First of all, my knowledge about SSIS is... equal zero!

If you want to insert values (...)starting from 000001 and increment continuously for each row(...), it's not so simple! Why? 000001 is not a numeric value, it's a text!

The below example shows how to add/insert 1K records.
DECLARE @sNum NVARCHAR(6)
DECLARE @iNum INT
DECLARE @iCount INT
DECLARE @iCounter INT

-- add 1000 rows, starting from 1
SET @iNum = 1 
SET @iCount = 1000
SET @iCounter = 1
WHILE (@iCounter <= @iCount)
BEGIN
	SET @sNum = '000000'
	SET @sNum = SUBSTRING(@sNum, LEN(@sNum) - LEN(CONVERT(VARCHAR(6), @iNum))+1, LEN(CONVERT(VARCHAR(6), @iNum))) + CONVERT(VARCHAR(6), @iNum)
	INSERT INTO [YourTable] ([YourField]) VALUES(@sNum)
	SET @iNum = @iNum + 1
	SET @iCounter = @iCounter + 1
END


Results:
000001<br />
000002<br />
...<br />
001000
   
Comments
VJ Reddy 15-May-12 4:45am
   
Good answer. 5!
Maciej Los 15-May-12 4:49am
   
Thank you, VJ ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100