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.CustomerInfo
INSERT INTO MyDatabaseABC.dbo.Customer
SELECT * FROM MyOtherDatabase.dbo.Custom
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
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspUpdateTableData]
AS
DELETE FROM MyDatabaseABC.CustomerInfo
INSERT INTO MyDatabaseABC.dbo.Customer
SELECT * FROM MyOtherDatabase.dbo.Custom
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
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..
ASKER
I keep getting error:
Msg 102, Level 15, State 1, Procedure uspUpdateTableData, Line 5
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Procedure uspUpdateTableData, Line 5
Incorrect syntax near '-'.
ASKER
My server name is: DD-01 (it has a dash). Is that the issue?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.