pass a variable to SET IDENTITY_INSERT ON or OF

Posted on 2003-02-20
Medium Priority
Last Modified: 2010-05-18
In my script i have a variable that stores the table name.
For example

DECLARE @table_name varchar(50)
SET table_name ='tblClients'

Now i'm able to pass this variable to many statements like this:
EXECUTE ('select * from' + @table_name + '.... bla bla bla')

but i not able to pass some parameters to the SET IDENTITY_INSERT. The following statements don't work:
EXECUTE ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
PRINT ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
SELECT ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')

Do you have some hints? Thanks in advance


Question by:bdario
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 7987430
You need to include Use [Database Name] in your string.

For example:

EXECUTE ('Use ' + @dbname + ' SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
PRINT ('Use ' + @dbname + ' SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7987570
What do you mean by "don't work"?  Remember that you can have only one table per session that has this on.  So, if you've already got a table in your session with insert identity on, you won't be able to set it on for a second table until you're set it back off for the first table.

Expert Comment

ID: 7988416
If using dymanic sql to perform an action, you should have the proper database permissions. for SET IDENTITY you need to have ddladmin or sysadmin permissions.

Accepted Solution

hakyemez earned 600 total points
ID: 8021225
;;;;;;;;;;;; SQL SYNTAX ROW DELIMETER ;;;;;;;;;;;;;;;
DECLARE @stmt1 VARCHAR(4000),@stmt2 VARCHAR(4000),@stmt3 VARCHAR(4000)

SET @stmt1='SET IDENTITY_INSERT ['+@tableName+'] ON'
SET @stmt2='INSERT INTO '+@tableName+' VALUES(....)'
SET @stmt3='SELECT * FROM blablabla2'

SET @stmt1 = @stmt1+';'+@stmt2+';'+@stmt3
EXECUTE @stmt1

Author Comment

ID: 8023654
Thanks to everyone for your suggestions, now i solved the problem!


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question