Sunday, September 29, 2013

[T-SQL] INSERT INTO MULTIPLE TABLES AT ONCE

[T-SQL] INSERT INTO MULTIPLE TABLES AT ONCE

INSERT INTO MULTIPLE TABLES AT ONCE

Posted: 08 Mar 2012 09:14 AM PST

Hello All,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 rowsI 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 m1Columns: C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12Table: TABLE1 t1Columns: ID, E1, E2, E3, E4, E5, E6Table: TABLE2 t2Columns: ID, E7, E8, E9, E10, E11, E12 INSERT INTO TABLE1, TABLE2(t1.E1, t1.E2, t1.E3, t1.E4, t1.E5, t1.E6,t2.E7, t2.E8, t2.E9, t2.E10, t2.E11, t2.E12)SELECT m1.C1, m1.C2, m1.C3, m1.C4, m1.C5, m1.C6m1.C7, m1.C8, m1.C9, m1.C10, m1.C11, m1.C12FROM MAIN_TABLE m1JOIN TABLE1 t1 ON m1.C1 = t1.E1JOIN TABLE2 t2 ON m1.C1 = t2.E7So, 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!!Ronnie
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog