Take dynamic sql result with variable 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;
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
Senior Developer
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;
Use unambiguous date literals if possible.
I will be asking another question based on this one in regards to having 2 parameters..
@Startdate @ Enddate
fordraiders
Sr. DBA
@SQL NVARCHAR(MAX);
SET @StartDate = '01-JAN-19';
SET @SQL = ' INSERT INTO Work_Flow.dbo.Customer_Cal
SELECT * FROM OPENQUERY(XREF_PROD, ''SELECT leavetype, leavereason FROM XREF.XREF_CALENDER WHERE createdon >= ''''' + @StartDate + ''''''')';
EXEC sp_executesql @SQL;
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions