SET IDENTITY_INSERT on table - SQL Server Forums

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)

SET IDENTITY_INSERT on table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-08 : 09:13:19

Angela writes "DECLARE @TableName as varchar (100)
SET @TableName="Customers"
SET IDENTITY_INSERT @TableName ON
...
SET IDENTITY_INSERT @TableName OFF

I got an error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@tablename'
Can somebody explain me what is wrong?
Why I can set IDENTITY_INSERT on a variable that represents a table name?

"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-04-08 : 11:29:01

You cant use @variable as a table name.

- Sekar

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-08 : 12:41:33

The only way to do this, as you have it shown, is to use dynamic SQL.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rev_robert
Starting Member

1 Post

Posted - 2004-05-05 : 13:54:54

Granted, Dynamic SQL must be used. But EXEC('SET IDENTITY_INSERT ' + @tablename + ' ON')
doesn't actually set the IDENTITY_INSERT ON. So, how do you?

Thanks,
Robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 13:56:42

Why would you want to do this? Why would you need to do this dynamically?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-05 : 14:40:11

DECLARE
@TableName AS NVARCHAR(256),
@sql AS NVARCHAR(2500)

SET @TableName="Customers"
SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON'
EXEC sp_executesql @sql

SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
EXEC sp_executesql @sql

That will work. I can't imagine a good scenario to use this for. This type of thing should be avoided by good design. What are you using it for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 14:46:47

The command is only active for the batch so as soon as your dynamic sql statement terminates the identity_insert will be set off. To do this you will also have to put the insert inside the dynamic sql string.

SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON insert ' + @TableName + '(id, ...) select 1, ....'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-05 : 14:54:54

So, your complete statement would be something like this:

-- CREATE TABLE djl_test_ident(
-- ident INT IDENTITY(1,1) PRIMARY KEY,
-- value VARCHAR(55))
--TRUNCATE TABLE djl_test_ident

INSERT djl_test_ident(value) SELECT 'test1'

DECLARE
@TableName AS NVARCHAR(256),
@sql AS NVARCHAR(2500)

SET @TableName='djl_test_ident'
SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON ' + 'INSERT ' + @TableName + '(ident, value) SELECT 50000,''' + 'test''' + 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
EXEC sp_executesql @sql

INSERT djl_test_ident(value) SELECT 'test2'

SELECT * FROM djl_test_ident

Look at the results of this carefully and consider the consequences though of what you will be doing.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RahulKate
Starting Member

1 Post

Posted - 2004-05-28 : 03:51:03

Hey Guys
I am also one of them,having the same problem. I have tried using the code below. And I am calling this code using Visual Basic. but it gives me error.

DECLARE
@TableName AS NVARCHAR(256),
@sql AS NVARCHAR(2500)

SET @TableName='djl_test_ident'
SET @sql = 'SET IDENTITY_INSERT ' + @TableName + ' ON ' + 'INSERT ' + @TableName + '(ident, value) SELECT 50000,''' + 'test''' + 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
EXEC sp_executesql @sql

INSERT djl_test_ident(value) SELECT 'test2'

Kindly Suggest!!!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 09:01:34

Put it into a stored procedure and call the stored proc from VB. You shouldn't be using inline code anway. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources