April 6, 2017 at 2:00 pm
In t-sql 2012, I want unique records (no duplicates) inserted into an empty table called DCert. The column that contains the duplicate values is [State ID].
Here is the sql that I have so far and the duplicate records are appearing on the insert statement.
Note: I do need the following where clause '([State ID] <> (' ')) AND ([State ID] is not null) AND len([State ID]) > 8
due to the rows that contain 'bad data'.
INSERT INTO DCert
(stateID, custnumber, lastname,firstname,mailAddress)
SELECT DISTINCT
DC.[State Id] as stateID
,DC.[ID] as studentnumber
,DC.[Last Name] as lastname
,DC.[First Name] as firstname
,Mailingaddress.mailAddress
FROM test1.dbo.CerImport DC
LEFT JOIN Mailingaddress Mailingaddress ON Mailingaddress.stateID=DC.[State Id]
where DC.[State ID] IN (
SELECT distinct [State ID]
FROM test1.dbo.CerImport
where ([State ID] <> (' ')) AND ([State ID] is not null)
AND len([State ID]) > 8
)
Thus can you show me the t-sql 2012 so that no duplicate records by [State ID], are not inserted into the DCert table?
April 6, 2017 at 3:19 pm
You can try this
INSERT INTO DCert (stateID, custnumber, lastname, firstname, mailAddress)
WITH cteData AS (
SELECT
DC.[State Id] as stateID
, DC.[ID] as studentnumber
, DC.[Last Name] as lastname
, DC.[First Name] as firstname
, Mailingaddress.mailAddress
, rn = ROW_NUMBER() OVER (PARTITION BY DC.[Last Name], DC.[First Name], Mailingaddress.mailAddress ORDER BY DC.[ID] DESC)
FROM test1.dbo.CerImport DC
LEFT JOIN Mailingaddress Mailingaddress ON Mailingaddress.stateID = DC.[State Id]
WHERE DC.[State ID] <> (' ')
AND [State ID] IS NOT NULL
AND LEN([State ID]) > 8
)
SELECT
cte.stateID
, cte.studentnumber
, cte.lastname
, cte.firstname
, cte.mailAddress
FROM cteData AS cte
WHERE cte.rn = 1;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply