?
Solved

limit Db insert on ID

Posted on 2011-02-16
2
Medium Priority
?
270 Views
Last Modified: 2012-05-11
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
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 34907094
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
 

Author Closing Comment

by:kdeutsch
ID: 34907172
Ok, see what I did wrong, thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question