Home Forums SQL Server 2008 T-SQL (SS2K8) How can I set identity_insert on a tablename passed to an sproc RE: How can I set identity_insert on a tablename passed to an sproc

  • December 22, 2010 at 5:01 pm

    #1265746

    since you said you are building a string, this is probably what you want for a demo:

    CREATE TABLE IDDemo

    ( pk_ID Integer IDENTITY(1,1)

    , Textdata Varchar(100) )

    DECLARE @sql varchar(max)

    declare @tablename varchar(30)

    SET @sql = 'SET IDENTITY_INSERT @TheDynamicTableName ON;INSERT @TheDynamicTableName (pk_ID, Textdata) Values (4, ''This works too'');SET IDENTITY_INSERT @TheDynamicTableName OFF;'

    SET @tablename = 'IDDemo'

    SET @sql = REPLACE(@sql,'@TheDynamicTableName',@tablename)

    PRINT @sql

    EXEC (@sql)

    SELECT * FROM IDDemo

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!