insert from another table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert from another table

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-24 : 17:20:52

Hi.I have 2 tables.they have join on the product_id field.
tbl_product & tbl_product_prop
I want to insert the product_id's from tbl_product wich are not on the second table(tbl_product_prop)

here is the join:
SELECT dbo.tbl_product.product_id AS Expr1
FROM dbo.tbl_product INNER JOIN
dbo.tbl_product_prop ON dbo.tbl_product.product_id = dbo.tbl_product_prop.product_id

how can I select & insert them.
thanks & Merry Christmas

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-24 : 17:32:24

now I have the select query like this:
SELECT product_id AS Expr1
FROM dbo.tbl_product
WHERE (NOT EXISTS(SELECT product_id
FROM tbl_product_prop
where tbl_product_prop.product_id =tbl_product.product_id ))

my question is hoe can I insert them to the second table?
thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 17:32:34

Insert tbl_Product_prop(Product_id)
select tp.Product_id
from tbl_product tp
left outer join tbl_product_prop tpp
on tp.product_id = tpp.product_id
where tpp.Product_id is null

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 17:35:40

Or in SQL 2005

Insert tbl_product_prop(product_id)
select t.Product_id from
(select product_id from tbl_product
except
select product_id from tbl_product_prop)t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-24 : 23:40:47

try this
u can use not in also

Insert tbl_product_prop(product_id)
select Product_id from tbl_product where product_id NOT IN (select product_id from tbl_product_prop)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources