• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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