Pass a variable to SET IDENTITY_INSERT

I'm quite new to programming with SQL Server.
I'm developing an SQL statement using Query Analyzer that read a table from a MSAccess Linked server database, and put the data in to a SQL server table.

I'd like that table name, db name, etc, are values stored in some variables, so, when i want to import another table, i need to change only the value of the @.table_name variable.

I can pass the value of this variable to all the statements of my script but one. I'm able to execute something like

EXEC ('select * from' + @.table_name + '... bla bla bla')

but i'm not able to execute the SET IDENTITY_INSERT @.table_name ON/OFF.
I tried many options like print, exec, quotes, double quotes, etc, but nothing to do.

I'd really appreciate all your helping answers.

Ciao

Darioif you wish to do this you will need to build a string containing the set statement and the insert statment. then EXEC(@.string).

Remember that EXEC() runs on a seperate thread than your current connection so any settings will be lost once the EXEC finishes.

declare @.TSQL varchar(1000)
CREATE TABLE #products (id int IDENTITY PRIMARY KEY, product varchar(40))

-- Inserting values into products table.
INSERT INTO #products (product) VALUES ('screwdriver')
INSERT INTO #products (product) VALUES ('hammer')

select * From #Products

-- Inserting ids and values into products table.
set @.TSQL = 'set identity_insert #products on
INSERT INTO #products (id,product) VALUES (10,''saw'')
INSERT INTO #products (id,product) VALUES (11,''shovel'')'

exec(@.TSQL)

select * from #products

drop table #Products|||The script is the following:

-- Variable declaration
DECLARE @.nome_db nvarchar(50)
DECLARE @.nome_tabella nvarchar(50)
DECLARE @.id_tabella int
DECLARE @.str_nome_campi nvarchar(255)
DECLARE @.str_tmp_nome_campo nvarchar(255)
DECLARE @.strdata nvarchar(255)
DECLARE @.str_sql nvarchar(255)
DECLARE @.str_sql_delete nvarchar(255)
DECLARE @.str_sql_use nvarchar(255)
DECLARE @.str_sql_debug nvarchar(255)
DECLARE @.str_id_insert_on nvarchar(50)
DECLARE @.str_id_insert_off nvarchar(50)

-- Set the variables
SET @.nome_db = 'Unitec_Script_Importazione'
SET @.nome_tabella = 'ANFRAGEDETAILS2'

SET @.str_sql_delete = 'DELETE FROM [' + @.nome_db + '].[dbo].[' + @.nome_tabella + ']'
SET @.str_sql_use ='USE [' + @.nome_db + ']'
SET @.str_sql_debug = 'SELECT TOP 10 * FROM ' + @.nome_tabella

-- Stringhe per settare Identity ... maledette!
SET @.str_id_insert_on = ' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' ON' + char(10)
SET @.str_id_insert_off =' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' OFF' + char(10)
-- Trovo, all'interno della tabella di sistema sysobjects, l'ID
-- della tabella. Usando questo valore posso trovare i campi relativi
SET @.id_tabella = (SELECT [id] FROM sysobjects WHERE [name] LIKE @.nome_tabella)
--SELECT @.id_tabella -- (debug - stampa il risultato della query)

DECLARE nome_campi CURSOR
FOR SELECT [name] FROM syscolumns where id = @.id_tabella
-- Open the cursor
OPEN nome_campi
-- Prendo la prima riga del cursore e la schiaffo dentro alla variabile temporanea
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo

SET CONCAT_NULL_YIELDS_NULL OFF

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str_nome_campi = @.str_tmp_nome_campo
SET @.strdata = @.str_nome_campi + @.strdata
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo
-- Aggiungo la virgola se necessario
SET @.str_tmp_nome_campo = @.str_tmp_nome_campo + ', '
END

SELECT @.strdata -- Debug: stampa il contenuto di strdata

CLOSE nome_campi
DEALLOCATE nome_campi

EXECUTE (@.str_sql_use)

EXECUTE sp_executesql @.str_sql_delete

/******* THIS IS WHERE I NEED HELP*********
Instead of the name of the table (ANFRAGEDETAILS2)
i want to use a variable @.table_name
*****************************************/
SET IDENTITY_INSERT ANFRAGEDETAILS2 ON

SET @.str_sql = @.str_sql + 'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')'

EXECUTE (@.str_sql)

SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF

--
--
--|||have you tried:

SET @.str_sql = @.str_sql + 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON
INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')
SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF'
EXECUTE (@.str_sql)|||Yes, 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON ... works
but 'SET IDENTITY_INSERT @.table_name ON ... Doesn't work|||AND
SET @.str_sql = @.str_sql
+ ' SET IDENTITY_INSERT [dbo].['+@.nome_tabella+'] ON '
+'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select '' + @.strdata + '' from ['' + @.nome_tabella + '']'')'
EXECUTE (@.str_sql)
WORKS !?|||Yes, it works!!!

Many thanks

Dario