Incrementing a local variable per row

[eao]

I need some help figuring out what I am doing wrong. I am trying to migrate data from one system to another, and I want to assign an incrementing number to a field, but I can't seem to nail down the particulars. I am starting to think I need a stored procedure to increment the field. Please let me know if I am on the right track. Thanks.

USE SDE
GO
--Variable to set the EBU.Sequence field.
DECLARE @Seq int

--Loop control.
DECLARE @Counter int

--Populate the record number table.
INSERT SDE.dbo.smsysrecnum (Name, RecNum)
	VALUES ('EBU', '1000')

--Set the counter to the number of records being imported.
SET @Counter = (SELECT Count (*) FROM HEAT.dbo.EBU)

WHILE @Counter > 0
BEGIN
	--Get the record number value for the EBU table.
	SET @Seq = (SELECT Recnum FROM SDE.dbo.smsysrecnum WHERE NAME = 'EBU')

	--Populate the temporary table with the data to be imported.
	-- **@Seq should increment for each row.**		
	SELECT (@Seq + 1) as NewSeq, name
		INTO #tmpB
		FROM HEAT.dbo.EBU as h LEFT OUTER JOIN SDE._smdba_.EBU AS s 
			ON h.name = s.EBU
		WHERE s.EBU is null
	SET @Counter = @Counter - 1
	
	--Update the record number table with the last used Seq value.
	UPDATE SDE.dbo.smsysrecnum
		SET RecNum = @Seq
		WHERE Name = 'EBU'

	SET @Seq = @Seq + 1
END

--Run the import.
INSERT INTO sde._smdba_.EBU (Sequence, EBU)
	SELECT NewSeq, name
	FROM #tmpB

DROP TABLE #tmpB

 

[ca8msm]

If you create a temporary table or table variable, you can specify an identity column and just insert the data into that table/variable e.g.

declare @mytable table (id int [!]IDENTITY(1,1)[/!], othercolumns...)

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[eao]

Does this look better?

USE SDE
GO
--Variable to set the EBU.Sequence field.
DECLARE @Seq int

--Table variable for EBU data.
declare @tmpEBU table (
	NewSeq int IDENTITY(1001,1), 
	Name nvarchar (30)
	)

--Populate the record number table.
INSERT SDE.dbo.smsysrecnum (Name, RecNum) 
	VALUES ('EBU', '1000')

--Populate the temporary table with the data to be imported.
-- **@Seq should increment for each row.**		
SELECT name
	INTO @tmpEBU
	FROM HEAT.dbo.EBU as h LEFT OUTER JOIN SDE._smdba_.EBU AS s 
		ON h.name = s.EBU
	WHERE s.EBU is null

--Run the import.
INSERT INTO SDE._SMDBA_.EBU (Sequence, EBU)
	SELECT NewSeq, name
	FROM @tmpEBU

--Get the last used sequence number.
SET @Seq = (SELECT max(sequence) FROM SDE._SMDBA_.EBU)

--Update the record number table with the last used Seq value.
UPDATE SDE.dbo.smsysrecnum
	SET RecNum = @Seq
	WHERE Name = 'EBU'

DROP TABLE @tmpEBU