• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

limit Db insert on ID

This worked for another table but does not seem to work for this insert statement, basically i don't want to insert into the other table if the SSAN already exists in the table.  What amd I doing wrong.
Insert Personnel.dbo.tblPersonnel (strSSN, strLName, strFName, strMName)
Select	LTRIM(RTRIM(Replace(SSAN, CHAR(160), ''))) SSAN,
		Substring(Name, 1, charindex(' ', name, 1)-1) Last,
		Substring(Name, charindex(' ', name, 1)+1, charindex(' ', name, charindex(' ', Name, 1)+1)-charindex(' ', name, 1)-1) First ,
		Case charindex(' ', Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1) when 0 then '' else 
		Substring(Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1, charindex(' ', Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1)-charindex(' ', name, charindex(' ', Name, 1)+1)-1) End Middle
FROM	[Personnel].[dbo].[tblTemp] where not Exists (Select null from tblPersonnel where SSAN = strSSN)

Open in new window

0
kdeutsch
Asked:
kdeutsch
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
Insert Personnel.dbo.tblPersonnel (strSSN, strLName, strFName, strMName)
Select  LTRIM(RTRIM(Replace(SSAN, CHAR(160), ''))) SSAN,
                Substring(Name, 1, charindex(' ', name, 1)-1) Last,
                Substring(Name, charindex(' ', name, 1)+1, charindex(' ', name, charindex(' ', Name, 1)+1)-charindex(' ', name, 1)-1) First ,
                Case charindex(' ', Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1) when 0 then '' else 
                Substring(Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1, charindex(' ', Name, charindex(' ', Name, charindex(' ', Name, 1)+1)+1)-charindex(' ', name, charindex(' ', Name, 1)+1)-1) End Middle
FROM    [Personnel].[dbo].[tblTemp] t
where not Exists (Select null from tblPersonnel p. where LTRIM(RTRIM(Replace(t.SSAN, CHAR(160), ''))) = p.strSSN)

Open in new window

0
 
kdeutschAuthor Commented:
Ok, see what I did wrong, thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now