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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert into table...where concat() = concat()

Author  Topic 

surrinkm
Starting Member

1 Post

Posted - 2012-11-29 : 00:45:35

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-29 : 01:33:07
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
   

- Advertisement -