Currently I have a SQL agent on SERVER1 performing the following example daily task to copy selected data from one server to another:
SELECT IDENTITY (int, 1, 1) AS RowID, view1.*, 'XYZ' AS SourceSvr INTO database.dbo.table1 FROM [SERVER2].database.dbo.view view
It seems that using stored procedures may be a better approach. I know how to create a sp and execute it, but how do I do a SELECT...INTO? I may have cases where the data needs to be pushed from SERVER2, and where it needs to be pulled from SERVER1.
Thank you for your ideas.
SELECT IDENTITY (int, 1, 1) AS RowID, view1.*, 'XYZ' AS SourceSvr INTO database.dbo.table1 FROM [SERVER2].database.dbo.view view
It seems that using stored procedures may be a better approach. I know how to create a sp and execute it, but how do I do a SELECT...INTO? I may have cases where the data needs to be pushed from SERVER2, and where it needs to be pulled from SERVER1.
Thank you for your ideas.
