October 3, 2008 at 1:57 pm
Does anyone know of a way to use multiple select statements in one insert statement?
For example:
insert into table1 (col1, col2, col3)
select data1 from table2 where data1 like 'precedeswithtest%', select data2 from table3, select data3 from table4 where data3 <> '%endswithtest'
I tried using the update statement, however a subquery can only bring back one value. In my case, I need it to bring back multiple values for instance:
table2's data will be inserted into col1. Ex: test1
test2
test3's data will be inserted into col2. EX: hello1
hello2
and the same with table3 EX: goodbye1
goodbye2
Final result after insert statement has been written properly should appear like:
col1 col2 col3
test1 hello1 goodbye1
test2 hello2 goodbye2
Any help would be greatly appreciated.
October 3, 2008 at 3:36 pm
How do the rows in table2 match up to rows in table3, match up to rows in table4?
You need some type of join between the 3 tables, but you're not providing enough info to help you write that out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2008 at 10:23 pm
Well, in the absence of any other information I'd suggest you try something like this:
INSERT into Table1(col1,col2,col3)
Select T2.data1, t3.data2, t4.data3
From (Select data1, row_number() Over(order by (1)) as RowNum From Table2) T2
Join (Select data2, row_number() Over(order by (1)) as RowNum From Table3) T3
ON T2.RowNum = T3.RowNum
Join (Select data3, row_number() Over(order by (1)) as RowNum From Table4) T4
ON T3.RowNum = T4.RowNum
Seems slightly crazy though...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 5, 2008 at 7:07 am
Hi
Your requirement is not clear, but based on my initial understanding is If you have join condition to join all these tables then you need to write one SELECT query.
If you dont have any columns to join and these are independent tables then use UNION ALL to concatinate all the queries.
INSERT INTO TEST
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
check out the syntax.
Thanks -- Vj
-- For Microsoft related queries
-- For Oracle related queries
October 5, 2008 at 7:55 am
You need columns for ordering the rows in each of your tables.
Are such columns available?
(Remember that per default the rows are unordered, so any select
against the 3 tables could return the data differently for each table
and therefore produce indeterministic results when combining the 3
columns into your resultset)
Best Regards,
Chris Büttner
October 6, 2008 at 7:49 am
I did try this, however each select statement became one record for each row. What I'm wanting to do is make it the same record. I'm selecting data from two different tables, but consolidating it into one record.
select col1 from dummy
select col1 from dummy2
insert into dummy3
select col1 from dummy, select col1 from dummy2
result:
dummy3 will contain the following information:
col1 col2
col1's data from dummy col1's data from dummy2
It appears from one of the posts, I have to join the tables however, if there is no relationships between the two tables, is there an alternative?
October 6, 2008 at 8:05 am
If there really is no relationship between the tables then does that mean that the data in each field of each row of the final table is not related to the other fields? That does not make a lot of sense. However, if that really is the case, you can use Barry's example. He created a RowNumber column for each table and then joined on that column. Try using his code, it should do the trick.
October 6, 2008 at 8:47 am
The row number does not help if there is no valid column for ordering the rows in each table.
So the question is still open by which column the rows in each table should be ordered before the columns are concatenated.
The example provided using row_number does not work without an explicit column used for ordering the rows (the column index cannot be specified by the way, it must be the column name).
Best Regards,
Chris Büttner
October 7, 2008 at 7:31 am
Thanks, that worked.
December 8, 2009 at 1:40 am
I need to insert into a table , and define the type of each reocrd depending on some conditions ( These conditions are specified in join / where clause), so We are using about 10 inserts for same table as we have 10 diffrent types ( New, existing, pre-existing) etc.
This is the current approach. Can any 1 suggest me any other alternative to insert the data froma coommon set of tables but with different join/ where conditions.
Thanks in advance !
December 8, 2009 at 8:39 am
ansz5 (12/8/2009)
I need to insert into a table , and define the type of each reocrd depending on some conditions ( These conditions are specified in join / where clause), so We are using about 10 inserts for same table as we have 10 diffrent types ( New, existing, pre-existing) etc.This is the current approach. Can any 1 suggest me any other alternative to insert the data froma coommon set of tables but with different join/ where conditions.
Thanks in advance !
Please post this question in its own thread.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply