Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Question Unanswered: 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

    Dario

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if 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.

    Code:
    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
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Posts
    4
    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

    --
    --
    --

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    have you tried:

    Code:
    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)
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2003
    Posts
    4
    Yes, 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON ... works
    but 'SET IDENTITY_INSERT @table_name ON ... Doesn't work

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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 !?
    Last edited by ispaleny; 02-21-03 at 08:29.

  7. #7
    Join Date
    Feb 2003
    Posts
    4
    Yes, it works!!!

    Many thanks

    Dario

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •