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)
DC.[State Id] as stateID
,DC.[ID] as studentnumber
,DC.[Last Name] as lastname
,DC.[First Name] as firstname
FROM test1.dbo.CerImport DC
LEFT JOIN Mailingaddress Mailingaddress ON Mailingaddress.stateID=DC.[State Id]
DC.[State ID] IN (
SELECT distinct [State ID]
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?