INSERT based on criteria - help please

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

SQL Server 2008 Forums

Transact-SQL (2008)

INSERT based on criteria - help please

Author

Topic

bigwang916
Starting Member

2 Posts

Posted - 2014-04-01 : 18:10:06

Hi everyone! I'm not sure if this is the right place to post and ask this question, I apologize if it is not.

I have 3 tables: Staff, Case, CasePermissions.
STAFF- StaffID, FirstName, LastName
CASE- CaseID, CaseDate
CASEPERMISSIONS- CasePermissionsID, StaffID, CaseID

CasePermissions shows which staff is authorized to what case. If David is an old employee that has access to 100 cases, a new employee John wants the SAME access to those cases. How do I script this out?

First I want to capture all of the cases from casePermissions what are assigned to David, then I would need to insert new rows in tbl.CasePermissions, but with John's userID.

Any help would be appreciated.

Thank you,

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-01 : 23:30:15

You can do the following
1) Capture all records of David(that needs to be updated) by using


SELECT CaseID
FROM CasePermissions a
INNER JOIN Staff b ON a.StaffId = b.StaffID
WHERE b.FirstName = 'David' AND b.LastName = 'Whatever_it_is'

2) You can directly update these record's staff id to JOHN's staffid. You won't have a history if you do. you can do that as below.


UPDATE a
SET a.StaffID = (SELECT StaffID FROM Staff WHERE FirstName = 'John' AND LastName = 'Whatever_it_is')
FROM CasePermissions a
INNER JOIN Staff b ON a.StaffId = b.StaffID
WHERE b.FirstName = 'David' AND b.LastName = 'Whatever_it_is'

or

3) If you want to maintain a history(which I don't see with any sort of dates being handled in the CasePermissions Table)


INSERT INTO CasePermissions(StaffID, CaseId)
SELECT (SELECT StaffID FROM Staff WHERE FirstName = 'John' AND LastName = 'Whatever_it_is'), a.CaseID
FROM CasePermissions a
INNER JOIN Staff b ON a.StaffId = b.StaffID
WHERE b.FirstName = 'David' AND b.LastName = 'Whatever_it_is'

I think you might be using 1 and 2 in your case.. Remember 1 is just for illustration purpose..

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

bigwang916
Starting Member

2 Posts

Posted - 2014-04-02 : 12:47:56

Thanks for the reply sqlsaga. I have tried your #3 solution because we want both employees to have the same cases so David can train John before David retires/leaves. This is my statement:
----------------------------------
INSERT INTO [CASEMANAGEMENTSYSTEM].[dbo].[casepermissions]
([caseid]
,[staffid])
select [caseid], 2 --this is John's ID
from casepermissions
where staffid = 1 --this is David's ID
--------------------------------------
The out put gives me this error:

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__casepermissions__6319B466'. Cannot insert duplicate key in object 'dbo.casepermissions'.
The statement has been terminated.----------
I believe the above error is due to possibly John already have a few cases manually assigned to him, therefore when trying to duplicate...it already detected that a case that David has is already assigned to John?

Thank you

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-02 : 14:35:16

what is the Unique key on the Casepermissions table say? Can you script it and paste it. If you have the unique key on CASEID and STAFFID combinedly , you shouldn't see this error. I believe you have the unique constraint is on just the CASEID. You might have to alter it to both the columns.

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources