dcsimg
Results 1 to 5 of 5

Thread: Dynamic SQL Statement help

  1. #1
    Join Date
    Nov 2002
    Posts
    231

    Dynamic SQL Statement help

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    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 '

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    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

  5. #5
    Join Date
    Nov 2002
    Posts
    231
    It is working as per Mak's suggestion. I need to generate insert script for production change.
    Thanks for your valuable inputs.
    Ravi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •