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.

Answer 1

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

try running:

exec dataMigration