INSERT INTO MULTIPLE TABLES AT ONCE

  • Ronnie Jones

    Hall of Fame

    Points: 3377

    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 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

    (

    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.C6

    m1.C7,

    m1.C8,

    m1.C9,

    m1.C10,

    m1.C11,

    m1.C12

    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!!

    Ronnie

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    You can not insert data into 2 tables simultaneously in a single session.

    But if u split the insert statements into 2 statements, it is going to give you the same effect! But make sure to add ORDER by in your SELECT statement for both the inserts.

    Good luck.!

  • R.P.Rozema

    SSChampion

    Points: 12300

    If you create a view over the both tables, having columns ID and E1 through E12 you can insert into both tables using 1 insert statement. Such a view is -without precautions- not updateable. But if you create an instead-of-insert-trigger on that view, you can hide inside that trigger the both inserts that are taking place. Effectively it will still be 2 inserts, but you only need a single insert statement to insert all columns at once. In the trigger you can use the "merge" command to insert columns E1 to E6 into the 1st table, which has an identity column called ID. Use the merge-command's output clause to retrieve the ID assigned to each newly inserted row and use that to insert the columns E7 to E12 into the second table, which also has an ID column, but here it is not an identity column.

    A word of warning: if you use the merge command (instead of an insert) against the view that has a merge command in it's instead of trigger, you'll have a server crash (thread will be aborted with a memory dump).



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • sam-1083699

    SSC Eights!

    Points: 843

    Hi

    I'm able to insert the data into multiple tables using dynamic sql.

    Please find the attachement.

    Index1 is my main table feeding data into index2,index3 and index4.

    i hope it helps you.

  • Eugene Elutin

    SSC Guru

    Points: 59322

    ColdCoffee (3/8/2012)


    You can not insert data into 2 tables simultaneously in a single session.

    But if u split the insert statements into 2 statements, it is going to give you the same effect! But make sure to add ORDER by in your SELECT statement for both the inserts.

    Good luck.!

    Depends on the what do you mean by "simultaneously" and "a single session"...

    I have couple of ideas how you could do it:

    1. Insert into Table1 which has a ON INSERT trigger which inserts everything into Table2

    2. INSERT with OUTPUT

    I'm not sure if it's simultaneously enough, but for it's definitely happens in a single transaction (if you want to call it "session")

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Ronnie Jones

    Hall of Fame

    Points: 3377

    This is pretty slick. I wouldn't have thought to use variables to store table information.

    In looking at your post, it appears that you are copying the same data from your main table into the different tables. I need to split the data from the main table between the 2 tables. And it's not a precise split either as well as for a couple columns, there are conditions on which column to use for that data which will probably result in the use of a CASE statement.

    I need to play around with your methods to see that that will work. But at any rate, I appreciate your help and examples.

    Thanks!!

    Ronnie

  • Ronnie Jones

    Hall of Fame

    Points: 3377

    Thanks to everyone for your input on this issue of mine. A lot of good information. I'll probably follow up with what I finally used to accomplish my task!

    Thanks again for all your help!

  • Sean Lange

    SSC Guru

    Points: 286491

    I agree with Eugene. Insert with OUTPUT will accomplish exactly what you are looking for.

    create table #abc

    (

    col1 int identity,

    col2 varchar(10)

    )

    create table #bbb

    (

    col1 int,

    col2 varchar(10)

    )

    insert #abc (col2)

    output inserted.col1, inserted.col2 into #bbb

    select 'My Value'

    select * from #abc

    select * from #bbb

    drop table #abc

    drop table #bbb

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ashkan sirous

    SSCrazy

    Points: 2726

    old topic, but it helped me find my solution

    Thanks a lot

    Best Regards,
    Ashkan

  • mbrady

    Old Hand

    Points: 302

    Thanks for this. Helped me too.

    My situation was that I have inherited a 372,000,000 row OLAP table that has several note-type columns in it. My task was to move those note columns into their own table to improve performance. Most of the extracts, views, procs and ad-hoc queries didn't need the note columns returned.

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

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