Click here to Skip to main content
14,388,220 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have 3 tables. i need to insert 3 column values from first table and 1 column value from second table into 3third table. what is the query for that????
Posted
Updated 2-Nov-11 21:41pm
v2
Comments
Prerak Patel 3-Nov-11 1:15am
   
Without knowing your table structure, it's difficult to help you.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Assuming column1 & column2 are in table1, column3 in table2:

insert into table3 (field1, field2, field3)
select column1, column2, column3 from table1 t1 join table2 t2 on t1.table_id = t2.table_id
   
Comments
lll1234 3-Nov-11 2:32am
   
I tried like this. Bit getting error


string insert = "insert into table3 (fld1,fld2,fld3) select (fld1,fld2,fld3) from table1 join table2 on table1.id=table2.id";
Rate this:
Please Sign up or sign in to vote.

Solution 2

http://www.java2s.com/Tutorial/Oracle/0080__Insert-Update-Delete/Combinethreetableswithinsertintostatement.htm[^]

Check the link. It creates three tables, populate them and then populate the fourth table from them. This would help you learn how you should proceed.
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

You can use SELECT INTO to do this
Ex:
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id


Read more about select into here[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

   
Rate this:
Please Sign up or sign in to vote.

Solution 5

u can something as:
INSERT INTO NewTable (SELECT tab1_col1, tab2.col2,tab3.col1 FROM   tab1, tab2,tab3 WHERE  Condtion)
   
v3
Rate this:
Please Sign up or sign in to vote.

Solution 6

If yor are directly inserting data i mean if you are passing values as a parameter then you can write direct query like

INSERT INTO table3(Code) Values ('aaa');

INSERT INTO MessageStatus (Code) Values ('Status01');

Or you can do following

1)If your new column is static then

declare @newColID int, @PQID int
Set @newColID = 1
set @PQID =1234

Insert into table3( [PQID] , [col1] , [col2], [newColID])
select PQID,col1,col2, @newColID
from TB1 where TB1.[PQID] = @PQID 

2)If your new column resides in another table(TB2)
If your new column is in a different table (related to TB1 through PQID)

Insert into table3( [PQID] , [col1] , [col2], [newColID])
select TB1.PQID,TB1.col1,TB1.col2, TB2.newColID
from TB1 INNER JOIN TB2 ON TB1.PQID = TB2.PQID
where TB1.[PQID] = @PQID

Or

Insert into table3( [PQID] , [col1] , [col2], [newColID])
select TB1.PQID,TB1.col1,TB1.col2, TB2.newColID
from TB1 , TB2
where TB1.[PQID] = @PQID
and TB1.PQID = TB2.PQID



Please let me know if it helps you.

Thanks

Prashant
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 8

Hi, use below query

Insert into table3( Field1 , Field2 , Field3)
select TB1.Field1,TB1.Field2,TB1.Field3
from TB1 inner join TB2
where TB1.prKey = TB2.FrKey
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100