Is it possible to insert records from one table into two separate tables? If not, based on the info below, what would you recommend?
Here is the scenario:
I have a table with approximately 15 columns and 60k rows
I want to insert the contents of that table into two existing empty tables. The important element of the insert is in the two tables, the first column in both tables is an identity auto increment column. I want to keep the rows consistent for that identity number between the two tables.
I know this code doesn’t work, but if it did, this is what it would look like:
Table: MAIN_TABLE m1
Columns: C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12
Table: TABLE1 t1
Columns: ID, E1, E2, E3, E4, E5, E6
Table: TABLE2 t2
Columns: ID, E7, E8, E9, E10, E11, E12
INSERT INTO TABLE1, TABLE2
FROM MAIN_TABLE m1
JOIN TABLE1 t1 ON m1.C1 = t1.E1
JOIN TABLE2 t2 ON m1.C1 = t2.E7
So, an entire row from MAIN_TABLE will be split between TABLE1 and TABLE2 and the ID column in TABLE1 and TABLE2 will increment by 1 and ID 1 in both tables should reflect the complete record taken from MAIN_TABLE row 1.
Hopefully that makes sense.
I appreciate your help!
Thanks in advance!!