Printable View
Hi, I try to get the dynamic insert statement script.
See the below statement I'm getting syntax error. How can change this right way script?.
select * into pubs.dbo.employee_temp
from pubs.dbo.employee
where emP_id<>emP_id
Declare @cmd varchar(8000)
set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+ 'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini t+''''+','+''''+lname+''''+')' from pubs.dbo.employee
EXECUTE sp_executesql @cmd
put
print @cmd
before execute to check how @cmd statement is formed.
This may work.
set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+
'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini
t+''''+','+''''+lname+''''+') from pubs.dbo.employee '
set concat_null_yields_null off
Declare @cmd varchar(8000)
select @cmd =@cmd +'insert into employee_temp(emp_id,fname,minit,lname) values '+ char(13)+char(10)+'Select '+'"'+emp_id+'","'+fname+'","'+minit+'","'+lname+' "'+char(13)+char(10) from pubs.dbo.employee
print @cmd
--EXECUTE sp_executesql @cmd
Call me crazy, but why are you doing this as a dynamic statement? Why aren't you just doing an Insert ... Select statement?
insert into employee_temp(emp_id, fname, minit, lname)
Select emp_id, fname, minit, lname
from pubs.dbo.employee
It is working as per Mak's suggestion. I need to generate insert script for production change.
Thanks for your valuable inputs.
Ravi