June 15, 2004 at 6:51 am
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
June 15, 2004 at 7:01 am
June 15, 2004 at 11:50 am
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