March 8, 2012 at 5:14 pm
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
March 8, 2012 at 5:51 pm
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.!
March 8, 2012 at 9:55 pm
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).
March 9, 2012 at 12:02 am
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.
March 9, 2012 at 3:43 am
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")
March 12, 2012 at 9:22 am
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
March 12, 2012 at 9:25 am
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!
March 12, 2012 at 9:54 am
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/
September 28, 2013 at 9:20 am
old topic, but it helped me find my solution
Thanks a lot
Best Regards,
Ashkan
October 28, 2013 at 11:26 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply