Post reply

EXEC the result set of a query?

  • October 20, 2006 at 4:18 pm

    #153246

    Anyone have the proper syntax I can execute the results of a query?

    Thanks!

    --What I've done so far

    declare @LinkedServer varchar(50) set @LinkedServer='INTERFLOW02'

    declare @X varchar(8000)

    --Here the query

    select 'insert openquery('+@LinkedServer+',''select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0'') VALUES('+cast(transmissionid as varchar)+','''+description+''','+cast(sortorder as varchar)+','+cast(active as varchar)+')'

    from Web_Inventory.dbo.Transmissions

    --Here is the results of the query...I really want execute these stmts

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(0,'None',0,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(1,'Automatic',1,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(2,'5 Speed Manual',2,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(3,'4 Speed Manual',3,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(4,'3-Speed Manual',4,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(5,'6-Speed Manual',5,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(6,'Allison Automatic',6,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(7,'Allison Manual',7,1)

    insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(8,'Automatic Overdrive',8,1)

    --I tried this, but it only executes the last statement

    declare @LinkedServer varchar(50) set @LinkedServer='INTERFLOW02'

    declare @X varchar(8000)

    select @X= 'insert openquery('+@LinkedServer+',''select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0'') VALUES('+cast(transmissionid as varchar)+','''+description+''','+cast(sortorder as varchar)+','+cast(active as varchar)+')'

    from Web_Inventory.dbo.Transmissions

    exec (@X)

  • October 20, 2006 at 5:10 pm

    #667232

    My bad.  I structured my search a couple different ways and found an answer.

    I see this has been asked a million times and xp_execresultset seems to be the answer.

    Anyone have any other suggestions as I am a bit leery of using this because it is undocumented.

    Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply