CODEWITHSUNDEEP
sql-servert-sqlinsertcopy
wellmannered
wellmannered

Reputation: 69

T-SQL Copy Row to Same Table and Another Table with Foreign Key

I'm using Transact-SQL, for example let's say I have...

TABLE_1

ID_MAIN  DATA
1        red
2        blue

TABLE_2

ID_SUB  ID_MAIN  TYPE
3       1        soft
4       2        rough

...and for each entry in "TABLE_1" i want to copy it to "TABLE_1" with a new unique id (primary key) and for each one of those entries in "TABLE_1" i also want to copy the entries that reference it (foreign key) in "TABLE_2" with a new id as well.

TABLE_1

ID_MAIN  DATA
1        red
2        blue
3        red
4        blue

TABLE_2

ID_SUB  ID_MAIN  TYPE
3       1        soft
4       2        rough
5       3        soft
6       4        rough

I understand how you can copy a entry and change certain fields as such

INSERT INTO TABLE_1 (ID_MAIN, DATA)
    SELECT NEWID(), DATA
    FROM TABLE_1

My question is more geared to how I can iterate threw each entry in TABLE_1, copy the entries with new ids, and per entry basis add new rows to TABLE_2 which reference those ids from TABLE_1

Hopefully this makes sense, not sure if it does to me haha

Thanks for any help!

Upvotes: 1

Views: 870

Answers (1)

Ronny
Ronny

Reputation: 157

If I understand you want something like:

INSERT INTO
    Table_2
    (
      table_2_id
      , table_1_id
      , type
    )
SELECT
    table_2_id = ROW_NUMBER() OVER (ORDER BY a.table_1_id)
    , table_1_id = a.table_1_id
    , type
      CASE
        WHEN a.data = 'red' THEN 'soft'
        WHEN a.data = 'blue' THEN 'rough'
      END
FROM
    table_1 a

This would take all records from Table 1, insert them into Table 2, assign the type based on the table 1 data column, and increment the ID for Table 2.

Upvotes: 1

Related Questions