Microsoft SQL server: copy table from linked server to current database using a stored procedure

Question

I am using Microsoft SQL server. the following code works if run from a QUERY:

SELECT * 
INTO mydatabase.dbo.atable 
FROM linkedserver.sandbox.dbo.atable

but it does not if inserted into a stored procedure:

SET ANSI_NULLS ON  GO 
SET QUOTED_IDENTIFIER ON  GO

ALTER PROCEDURE dataMigration   
AS  BEGIN   
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.     

 SET NOCOUNT ON;

 -- Insert statements for procedure here     
SELECT *     
INTO mydatabase.dbo.atable  
FROM linkedserver.sandbox.dbo.atable   

 END   
GO

Command(s) completes successfully but no table is created into mydatabase. Sorry for the trivial question. I had a look at similar issues but i did not find a case similar to mine.

Thank you for your help.

atable might or not exist into mydatabase. Once copied from linkedserver.sandbox.dbo.atable will be used to populate other tables on mydatabase. — user1536396, Oct 28 '15 at 16:08
Try with: `SELECT * INTO mydatabase.dbo.atable FROM OPENQUERY([linkedserver], 'SELECT * FROM sandbox.dbo.atable')` — Lukasz Szozda, Oct 28 '15 at 16:11
One thing does your table contain VARBINARY/XML column in remote server? — Lukasz Szozda, Oct 28 '15 at 16:14

score 3 · Accepted Answer · answered Oct 28 '15 at 16:09

3

You have to execute the stored Procedure after you run the code to alter it.

try running:

exec dataMigration

answered Oct 28 '15 at 16:09

Phillip Byram

60
6

score 1 · Answer 2 · answered Oct 28 '15 at 16:16

1

Right clickOption Image the store procedure and click "Execute Store procedure"

answered Oct 28 '15 at 16:16

Mytroy2050

179
1
2
17

Microsoft SQL server: copy table from linked server to current database using a stored procedure

2 Answers2