May 3, 2018 at 12:17 pm
Below is some example data. First, I pull everything from the table person. Then, I need to check if that personid already exists in the table process with the activityid of 991. If this is true then the fieldvalue and datelstmod can be updated if the field value is different.
If the record does not exist in the table process then the row can be inserted with an activityid of 991.
CREATE TABLE process
(
personid int,
activityid int,
fieldvalue varchar(25),
dateadded datetime2,
datelstmod datetime2,
processid int identity(1,1)
)
INSERT INTO process VALUES
(111, 991, 'Yes', getdate(), getdate()),
(111, 890, 'Other', getdate(), getdate()),
(222, 991, 'Yes', getdate(), getdate())
CREATE TABLE person
(
personid int,
requestind varchar(25)
)
INSERT INTO person VALUES
(111, 'Yes'),
(333, 'Yes'),
(222, 'No'),
(444, 'No'),
(888, 'Yes'),
(999, 'Yes')
The results would then look something like this:111 991 Yes 2018-05-03 12:51:40.0500000 2018-05-03 12:51:40.0500000 1
111 890 Other 2018-05-03 12:51:40.0500000 2018-05-03 12:51:40.0500000 2
222 991 No 2018-05-03 12:51:40.0500000 2018-05-03 12:56:60.0100000 3
888 991 Yes 2018-05-03 12:56:60.0100000 2018-05-03 12:56:60.0100000 4
999 991 Yes 2018-05-03 12:56:60.0100000 2018-05-03 12:56:60.0100000 5
444 991 No 2018-05-03 12:56:60.0100000 2018-05-03 12:56:60.0100000 6
333 991 Yes 2018-05-03 12:56:60.0100000 2018-05-03 12:56:60.0100000 7
May 3, 2018 at 12:44 pm
UPDATE prc
SET fieldvalue = prs.requestind
, prc.datelstmod = GetDate()
FROM dbo.process prc
JOIN dbo.person prs
ON prc.personid = prs.personid;
May 3, 2018 at 12:58 pm
UPDATE pr
SET fieldvalue = pe.requestind,
datelstmod = SYSDATETIME()
FROM process pr
INNER JOIN person pe ON pe.personid = pr.personid AND pr.activityid = 991
WHERE pr.fieldvalue <> pe.requestind
INSERT INTO process ( personid, activityid, fieldvalue, dateadded )
SELECT pe.personid, 991, pe.requestind, SYSDATETIME()
FROM person pe
WHERE NOT EXISTS(
SELECT 1
FROM process pr
WHERE pr.personid = pe.personid AND pr.activityid = 991)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 4, 2018 at 9:06 am
Joe Torre - Thursday, May 3, 2018 12:44 PMUPDATE prc
SET fieldvalue = prs.requestind
, prc.datelstmod = GetDate()
FROM dbo.process prc
JOIN dbo.person prs
ON prc.personid = prs.personid;
Thanks for the suggestion. However, I think this would miss the records that need to be inserted because they aren't present to update.
May 4, 2018 at 9:06 am
ScottPletcher - Thursday, May 3, 2018 12:58 PM
UPDATE pr
SET fieldvalue = pe.requestind,
datelstmod = SYSDATETIME()
FROM process pr
INNER JOIN person pe ON pe.personid = pr.personid AND pr.activityid = 991
WHERE pr.fieldvalue <> pe.requestindINSERT INTO process ( personid, activityid, fieldvalue, dateadded )
SELECT pe.personid, 991, pe.requestind, SYSDATETIME()
FROM person pe
WHERE NOT EXISTS(
SELECT 1
FROM process pr
WHERE pr.personid = pe.personid AND pr.activityid = 991)
Thanks. I'll give this a shot in my dev environment.
May 4, 2018 at 10:02 am
You're welcome, let me know how it goes.
Btw, it's inconsistent to use data type "datetime2" and GETDATE(), which returns only a "datetime" value. That's why I used SYSDATETIME() in my code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 4, 2018 at 10:17 am
Great, thanks for the tip!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply