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

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
DECLARE @StartDate AS VARCHAR(10),
      @SQL NVARCHAR(MAX);

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

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

   EXEC sp_executesql @SQL;
ste5anSenior Developer

Commented:
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

Author

Commented:
Thanks to both answers..

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

@Startdate  @ Enddate

fordraiders
Andrei FomitchevSr. DBA

Commented:
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;
Andrei FomitchevSr. DBA

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial