Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert into table...where concat() = concat()
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/29/2012 :  00:45:35  Show Profile  Reply with Quote

I have two tables (Accident & Employee) and I need to insert the correct EmployeeID from Employee table into the Accident table, to the record with the corresponding name.There are many duplicate names and some without middle names.(EmployeeID will be a foreign key in Accident table)

How do you insert data based on the comparison of two concatenated strings consisting of 3 fields?
Below is my stor_proc that I tried to execute.


create procedure sp_InsertID
DECLARE @fullname1 varchar(90);
DECLARE @fullname2 varchar(90);

SET @fullname1 = (Select CONCAT([FirstName],' ',COALESCE([MI],''),' ',[LastName]) FROM [dbo].[AccidentTEST]);
SET @fullname2 = (Select CONCAT([FirstName],' ',COALESCE([MI],''),' ',[LastName]) FROM [dbo].[EmployeeTEST]);

insert into [dbo].[AccidentTEST](EmployeeID)
(select EmployeeID from [dbo].[EmployeeTEST]
where @fullname1 = @fullname2)



Flowing Fount of Yak Knowledge

2242 Posts

Posted - 11/29/2012 :  01:33:07  Show Profile  Reply with Quote
i think you supposed to select first_name, mid_name and last_name from front-end and then the corresponding employee_id should be inserted into AccidentTEST table...

If this is ur requirement, do as follows:

create procedure sp_InsertID
( @p_FirstName VARCHAR(30),  --Mandatory
  @p_MidName VARCHAR(30),    --Optional
  @p_LastName VARCHAR(30)    --Mandatory
	insert into [dbo].[AccidentTEST](EmployeeID)
	select EmployeeID 
	from [dbo].[EmployeeTEST] 
	where CONCAT([FirstName],' ',COALESCE([MI],''),' ',[LastName]) = CONCAT(@p_FirstName,' ',COALESCE(@p_MidName,''),' ',@p_LastName) 

According to ur code, what i unserstood is you have already first_name,mid_name and last_name in AccidentTEST table and you are trying to pull corresponding employee_id from EmployeeTEST table into AccidentTEST table. For this u have to do update but not insert

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.32 seconds. Powered By: Snitz Forums 2000