IDENTITY_INSERT and table variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IDENTITY_INSERT and table variables

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2006-02-15 : 05:50:51

Hi

I have the following query that uses a looping function around a table variable. i have an identity coloumn on the variable but cannot insert values in to the table. It complains that "An explicit value for the identity column in table '@tbl' can only be specified when a column list is used and IDENTITY_INSERT is ON."

I've tried adding the set IDENTITY_INSERT statement but i get errors.

Any ideas how i can set IDENTITY_INSERT to on on a table variable.




Declare @tbl table
 (RowID INT IDENTITY(1, 1),
MarketMakerName varchar(20),
 ISIN varchar(20))

SET IDENTITY_INSERT @tbl ON

DECLARE @Strmm VARCHAR(20), StrISIN varchar(20)
Insert @tbl
SELECT 
	DISTINCT
	MarketMakerName, ISIN
FROM
	SecurityPermissions (NOLOCK)
	INNER JOIN MarketMakerDetails (NOLOCK) ON SecurityPermissions.MarketMakerID = MarketMakerDetails.MarketMakerID
WHERE
	SecurityPermissions.Permit = 1
ORDER BY
	MarketMakerDetails.MarketMakerName

SET @count = @@ROWCOUNT
/*initialize index counter*/
SET @iRow = 1
/*establish loop structure*/
WHILE @iRow <= @count
BEGIN
	SET @sCommand = 
		'UPDATE #tmpReport SET [' + @strMM + '] = 1 WHERE     #tmpReport.ISIN = ''' + @strIS + ''''
		--EXEC(@sCommand)

		print @sCommand

	SET @iRow = @iRow + 1
END

Thanxs OMB

OMB
Yak Posting Veteran

88 Posts

Posted - 2006-02-15 : 06:12:10

Found the answer

problem was in the insert statement. i forgot to add the column names. DOH

Thanxs

OMB

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-15 : 07:06:01

Are you able to set Identity_insert on/off on a table variable?

OMB
Yak Posting Veteran

88 Posts

Posted - 2006-02-15 : 07:18:39

Not sure, may be someone on here can answer that. the identity_insert was a bit of a red herring the real problem was that i did not specify the coloumn names in the insert statement. I guess, you dont need to have identity insert set to on if you do add the column names in the insert statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources