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

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

surrinkm
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
as
begin
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)

end

------------------

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/29/2012 :  01:33:07  Show Profile  Reply with Quote
Case-1:
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
)
as 
BEGIN
	insert into [dbo].[AccidentTEST](EmployeeID)
	select EmployeeID 
	from [dbo].[EmployeeTEST] 
	where CONCAT([FirstName],' ',COALESCE([MI],''),' ',[LastName]) = CONCAT(@p_FirstName,' ',COALESCE(@p_MidName,''),' ',@p_LastName) 
END


Case-2:
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



--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000