如何在SSIS中自动生成数字并在执行SQL任务中使用 [英] How to Autogenerate an numer in ssis and use in execute sql task

问题描述

朋友们...

我想知道如何自动生成一个连续的数字，并使用执行SQL任务在表中插入该数字...

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

从上面的URL，我学会了使用脚本组件任务自动生成数字，但是我想知道在插入查询中使用上面的数字...
例如，我需要生成一个从000001开始的数字，并为每一行连续递增，并通过执行sql任务将数字插入到插入查询中....


谢谢
Nithya.

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.

推荐答案

首先，我对SSIS的了解是……等于零！

如果要插入从000001开始的值(...)并为每行(...)连续递增，那不是那么简单！为什么? 000001不是数字值，而是文本！

以下示例显示了如何添加/插入1K记录.

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

这篇关于如何在SSIS中自动生成数字并在执行SQL任务中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！