Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

Take dynamic sql result with variable and place into a table on sql server

Take dynamic sql result and place into a table on sql server

I have this Dynamic sql query working fine.

DECLARE @StartDate AS VARCHAR(10),
      @SQL NVARCHAR(MAX);

SET @StartDate = '01-JAN-19';

SET @SQL = 'SELECT  * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER  WHERE createdon >= ''''' + @StartDate + ''''''')';

   EXEC sp_executesql @SQL;

Open in new window


I need to take this result now and INSERT it into table on sql server.
Database name =  Work_Flow
Table Name=  Customer_Calendar
Fields =   leavetype, leavereason

I have looked at kinds of examples on the internet..but gets confusing because most of the examples use a temp table.

Thanks
fordraiders
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Andrei Fomitchev

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ste5an

E.g.

DECLARE @StartDate AS VARCHAR(10) = '2019-01-19';
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT Q.leavetype, Q.leavereason
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER WHERE createdon >= {D}''
    ) Q;
';

SET @Statement = REPLACE(@Statement, '{D}', '''''' + @StartDate + '''''');

INSERT INTO Work_Flow.Customer_Calendar ( leavetype ,
                                          leavereason )
EXECUTE sys.sp_executesql @Statement;

Open in new window


Use unambiguous date literals if possible.
Fordraiders

ASKER
Thanks to both answers..

I will be asking another question based on this one in regards to having 2 parameters..

@Startdate  @ Enddate

fordraiders
Andrei Fomitchev

DECLARE @StartDate AS VARCHAR(10),
      @SQL NVARCHAR(MAX);

SET @StartDate = '01-JAN-19';

SET @SQL = ' INSERT INTO Work_Flow.dbo.Customer_Calendar
SELECT  * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER  WHERE createdon >= ''''' + @StartDate + ''''''')';

   EXEC sp_executesql @SQL;
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Andrei Fomitchev

INSERT INTO Work_Flow.dbo.Customer_Calendar (leavetype, leavereason) ...