尝试仅插入新记录。基于新的用户 ID 或现有用户 ID 和新的许可证状态。 在使我的 WHERE 子句正确时遇到问题。
下面是示例代码:
CREATE TABLE #SourceData
(
UserID varchar(30),
LicenseState varchar(2),
LicenseNumber varchar(10),
LicenseYear int
)
CREATE TABLE #Person
(
PersonID int,
LastName varchar(30),
FirstName varchar(30),
DomainAccountName varchar(50)
)
CREATE TABLE #License
(
LicenseID int IDENTITY(1,1) NOT NULL,
PersonID int,
LicenseState varchar(2),
LicenseNumber varchar(10),
LicenseYear int
)
-- Load #SourceData
INSERT INTO #SourceData (UserID, LicenseState, LicenseNumber, LicenseYear)
VALUES ('gwashington', 'CA', '11111', 1999);
INSERT INTO #SourceData (UserID, LicenseState, LicenseNumber, LicenseYear)
VALUES ('gwashington', 'TX', '9999', 2001);
INSERT INTO #SourceData (UserID, LicenseState, LicenseNumber, LicenseYear)
VALUES ('jadams', 'CA', '22222', 2000);
INSERT INTO #SourceData (UserID, LicenseState, LicenseNumber, LicenseYear)
VALUES ('tjefferson', 'TX', '44444', 2002);
INSERT INTO #SourceData (UserID, LicenseState, LicenseNumber, LicenseYear)
VALUES ('alincoln', 'IL', '55555', 2012);
-- Load #Person
INSERT INTO #Person (PersonID, LastName, FirstName, DomainAccountName)
VALUES (1, 'Washington', 'George', 'gwashington');
INSERT INTO #Person (PersonID, LastName, FirstName, DomainAccountName)
VALUES (2, 'Adams', 'John', 'jadams');
INSERT INTO #Person (PersonID, LastName, FirstName, DomainAccountName)
VALUES (3, 'Jefferson', 'Thomas', 'tjefferson');
INSERT INTO #Person (PersonID, LastName, FirstName, DomainAccountName)
VALUES (4, 'Lincoln', 'Abraham', 'alincoln');
-- Load #License
INSERT INTO #License (PersonID, LicenseState, LicenseNumber, LicenseYear)
VALUES (1, 'CA', '11111', 1999);
INSERT INTO #License (PersonID, LicenseState, LicenseNumber, LicenseYear)
VALUES (2, 'CA', '22222', 2000);
INSERT INTO #License (PersonID, LicenseState, LicenseNumber, LicenseYear)
VALUES (3, 'TX', '44444', 2002);
INSERT INTO #License (PersonID, LicenseState, LicenseNumber, LicenseYear)
VALUES (3, 'OH', '33333', 2008);
我尝试了以下方法:
INSERT INTO #License (PersonID, LicenseState, LicenseNumber, LicenseYear)
SELECT
person.PersonID,
srce.LicenseState,
srce.LicenseNumber,
srce.LicenseYear
FROM #Person person
LEFT JOIN #SourceData srce
ON person.DomainAccountName = srce.UserID
LEFT JOIN #License lic
ON person.PersonID = lic.PersonID
WHERE lic.PersonID IS NULL
OR (lic.PersonID IS NOT NULL AND lic.LicenseState <> srce.LicenseState)
或
WHERE lic.PersonID IS NULL
OR (lic.PersonID IS NOT NULL AND lic.LicenseState NOT IN ('CA','OH','DC','WA','IL','NY','DE','CO','HI','NJ','PA','VA','FL','TX','CT'))
以下是 #SourceData 中的数据:
UserID LicenseState LicenseNumber LicenseYear
gwashington CA 11111 1999
gwashington TX 9999 2001
jadams CA 22222 2000
tjefferson TX 44444 2002
alincoln IL 55555 2012
以下是 #Person 中的数据:
PersonID LastName FirstName DomainAccountName
1 Washington George gwashington
2 Adams John jadams
3 Jefferson Thomas tjefferson
4 Lincoln Abraham alincoln
以下是插入之前 #License 中的数据:
LicenseID PersonID LicenseState LicenseNumber LicenseYear
1 1 CA 11111 1999
2 2 CA 22222 2000
3 3 TX 44444 2002
4 3 OH 33333 2008
下面是所需的输出:
LicenseID PersonID LicenseState LicenseNumber LicenseYear
1 1 CA 11111 1999
2 2 CA 22222 2000
3 3 TX 44444 2002
4 3 OH 33333 2008
5 1 TX 9999 2001
6 4 IL 55555 2012
因此,应该添加gwashington和TX以及alincoln&IL。我可以添加alincoln和IL,但gwashington&TX没有。 此外,INSERT-SELECT 会继续插入记录,尽管它们已入。 它应该只有新记录要从尚未从 #SourceData 加载的记录中 #License。
感谢您的任何帮助。
看看这是否适合您:
INSERT INTO #License
SELECT p.PersonID,
s.LicenseState,
s.LicenseNumber,
s.LicenseYear
FROM #SourceData s
JOIN #Person p
ON s.UserID = p.DomainAccountName
LEFT JOIN #License l
ON l.PersonID = p.PersonID
AND l.LicenseState = s.LicenseState
WHERE l.PersonID IS NULL
有更好的方法可以做到这一点,但在无法测试任何 atm 的情况下,解决方法可能对中小型桌子大小有所帮助。
如果我正确解释数据,下面应该给出结果表:
WITH BASE AS (
SELECT
CONCAT(lic.PersonID, lic.LicenseState) AS IDxLicense
FROM #License lic
)
SELECT
person.PersonID,
srce.LicenseState,
srce.LicenseNumber,
srce.LicenseYear
FROM #Person person
LEFT JOIN #SourceData srce
ON person.DomainAccountName = srce.UserID
LEFT JOIN #License lic
ON person.PersonID = lic.PersonID
WHERE (CONCAT(person.PersonID, srce.LicenseState) NOT IN (SELECT IDxLicense FROM BASE))