CODEWITHSUNDEEP
sqlsql-server-2012
Jai
Jai

Reputation: 466

how to create a insert statement for values returned by a select query

I have a following select query:

select productid from product where productid not in (select productid from userproduct)

which gives me following output:

1
2
3
4

Now, i have another table userproduct which conatins following columns:

userid
productid
updatetime
author

I want to create following insert statements through sql:

insert into userproduct values(100,1,getdate(),'abc');
insert into userproduct values(100,2,getdate(),'abc');
insert into userproduct values(100,3,getdate(),'abc');
insert into userproduct values(100,4,getdate(),'abc');
insert into userproduct values(200,1,getdate(),'abc');
insert into userproduct values(200,2,getdate(),'abc');
insert into userproduct values(200,3,getdate(),'abc');
insert into userproduct values(200,4,getdate(),'abc');

Just for demo purpose I have used 4 values. But actually, i'll get hundreds of values. I can't create insert statements for all of them. Is there any way to create the insert statements automatically?

Upvotes: 0

Views: 153

Answers (3)

Kapil
Kapil

Reputation: 987

If 100 & 200 are only two distinct userids and 'abc' is the author value always, then

insert into userproduct (userid,productid,updatetime,author)
select 100,productid,getdate(),'abc' from product where productid not in (select productid from userproduct) 
Union
select 200,productid,getdate(),'abc' from product where productid not in(select productid from userproduct)

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: If you have limited userid to insert and not maintained in the table. you can use CROSS JOIN with static userids as below and you can add few more if needed:

INSERT INTO userproduct (userid,productid,updatetime,author)
SELECT x.userid, p.productid, getdate(),'abc' 
FROM product p
CROSS JOIN (SELECT userid FROM (values(100),(200)) x (userid)) x
WHERE productid NOT IN(SELECT productid FROM userproduct)

We can use tablename in the CROSS JOIN if users are maintained in any table.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Use an insert select statement. Cross join your results with the users table.

insert into userproduct (userid, productid, updatetime, author)
  select u.userid, p.productid, getdate(), 'abc'
  from product p
  cross join users u
  where p.productid not in (select productid from userproduct) ;

(You can of course add add criteria to restrict this to certain users, e.g. and u.userid in (100,200).)

(Or replace cross join users u with cross join (select 100 as userid union all select 200) u, if you don't want to access the users table for some reason.)

Upvotes: 1

Related Questions