Use table name in a variable?

  • My task is to read one table, get the name of another table and store in a variable, then use that variable to INSERT into the table whose name is contained in the variable. Is this possible? 

    -- First cursor gets all the table names

    USE NewSYS

    DECLARE Code_table_cursor CURSOR FOR

       SELECT Code_Table

       FROM tCode_cols b

       WHERE (SELECT Row_id FROM tCode_vals a

              WHERE a.New_value <> ' ')= b.Row_id

    OPEN Code_table_cursor

    -- =============================================

    DECLARE @Code_tab nvarchar(50), @seq_num smallint, @New_code char(2), @New_desc char(20)

    FETCH NEXT FROM Code_table_cursor

    INTO @Code_tab

    -- Fetch all the previously read table names

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

        DECLARE Code_value_cursor CURSOR FOR

        SELECT Seq_nbr, New_value, New_description

        FROM tCode_vals b

        WHERE (SELECT Row_id FROM tCode_cols a

               WHERE a.code_table = @Code_tab)= b.Row_id

        OPEN Code_value_cursor

    -- Using table name stored in variable @Code_tab INSERT the values   

    WHILE (@@FETCH_STATUS = 0)

        BEGIN  

            INSERT INTO @Code_tab VALUES (@New_code, @seq_num, @New_desc, ('19000101'), ('99991231'), 'CONVERSN', GETDATE())

            FETCH NEXT FROM Code_value_cursor

            INTO @seq_num, @New_code, @New_desc

        END

        FETCH NEXT FROM Code_value_cursor

        INTO @seq_num, @New_code, @New_desc

    END

    CLOSE Code_table_cursor

     

  • Try using Dynamic SQL

    Define a variable @DSQL as varchar(1000)

    On the inside of your cursor loop

    SET @DSQL = 'INSERT INTO ' + @Code_tab + ' VALUES (@New_code, @seq_num, @New_desc, ("19000101"), ("99991231"), "CONVERSN", GETDATE())'

    exec(@DSQL)

    note the need to change the extant single quotes to double

  • Thanks very much "warey"!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply