Using Stored Proceedure to Copy Table Data from Server A to Server B

USE [MyDatabaseABC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspUpdateTableData]
AS
DELETE FROM MyDatabaseABC.CustomerInformation;
INSERT INTO MyDatabaseABC.dbo.CustomerInformation
SELECT * FROM MyOtherDatabase.dbo.CustomerInformation;

The issue is the SELECT * FROM is a different server. How do I code this to look at a different server? Can  I do this? My goal is to remove all data and repopulate it with data from a different server. The new data is SQL 2008 the old data is on SQL 2005
allenkentAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Yes.  Put brackets around the server name; for example:


SELECT * FROM [DD-01].MyOtherDatabase.dbo.CustomerInformation;
0
 
John_VidmarCommented:
A 4-part object reference should do the trick:

USE [MyDatabaseABC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspUpdateTableData]
AS
DELETE FROM MyDatabaseABC.CustomerInformation;
INSERT INTO MyDatabaseABC.dbo.CustomerInformation
SELECT * FROM OtherServer.MyOtherDatabase.dbo.CustomerInformation;
0
 
Scott PletcherSenior DBACommented:
Be sure the data volume is low enough that you really want to do a delete and re-insert.  You could use Change Tracking, as one example, to just determine modifications (DELETE, INSERT, UPDATE) and apply those instead.  Or delayed replication, etc..
0
 
allenkentAuthor Commented:
I keep getting error:

Msg 102, Level 15, State 1, Procedure uspUpdateTableData, Line 5
Incorrect syntax near '-'.
0
 
allenkentAuthor Commented:
My server name is:   DD-01   (it has a dash). Is that the issue?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.