CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Jack James
Jack James

Reputation: 158

Dynamically Insert into table while checking if the record already exists

I had some doubts on dynamic insertion of data while doing an insert statement so just wanted to get some assistance from you guys. I have to do multiple insert statements say around 1500 records based on 2 different criteria's below is just a sample of 1 insert statement. Now while doing an insert statement I want to dynamically assign the USERID's and ROLEid's the 2 columns which you can see in the query below. So for example where userid IN (500 different userid) and role id in (100 different) ones. 

Insert into userrolelist (Userid, Roleid, IsDefault, EffectiveStart, 
EffectiveEnd, Clientid, LastmodifiedUserId, LastmodifiedTimestamp)
Values (161514,1011,1,'2016-01-21 00:00:00.001',Null,16785,0,'2016-01-21 
00:00:00.001')

I am sure there is a way to do dynamic insertion based on 2 different criteria's I am just confused as to how can I achieve that. Mainly also because for each criteria before insertion I need to check if that userid + roleid combination already exists in the table. Because if I dont check it and still do an insert it will throw an error because there is a constraint based on the 2 fields.

Any help on this matter would be appreciated. Please let me know if the question is not very clear and i can add a bit more explanation if required. Thank you.

Upvotes: 0

Views: 1746

Answers (1)

rd_nielsen
rd_nielsen

Reputation: 2459

You don't say where your lists of user IDs and role IDs are coming from, but because you specify different numbers for each of them, I assume that they are separate lists, rather than a single list of pairs. And I assume that they are stored in tables named userlist and rolelist, respectively. Then you can do the insert as follows:

insert into userrolelist
    (Userid, Roleid, IsDefault, EffectiveStart, EffectiveEnd, 
    Clientid, LastmodifiedUserId, LastmodifiedTimestamp)
select
    userid, roleid,
    1,'2016-01-21 00:00:00.001',Null,16785,0,
    '2016-01-21 00:00:00.001'
from
    (select userid, roleid 
    from userlist
        cross join rolelist
    ) as userrole
where
    not exists (select 1 from userrolelist as ur where ur.userid=userrole.userid and ur.roleid=userrole.roleid);

The subquery constructs a list of all possible pairs of users and roles, so if you already have a list of pairs, you can simply use that in place of the subquery.

Upvotes: 1

Related Questions