Copying a single row from another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying a single row from another server

Author

Topic

Chirag23
Starting Member

13 Posts

Posted - 2011-09-30 : 07:26:22

Hi

I have got 2 sql servers with identical databases but one is simply renamed as a test:

PRODserver - data.mdf
TESTserver - datatest.mdf

I recently restored the TESTserver with the latest backup from the PRODserver.

I would now like to restore just 1 row from 1 table from the TESTserver into the PRODserver.

Does anybody know the correct syntax to be able to do this?

Thanks

Chirag

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 07:28:50

why do you need a restore for that? wont a simple insert/update query based on linked server connection be anough?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-09-30 : 11:32:51

If it's just about one row, you can also write a query to update the prod db.

Chirag23
Starting Member

13 Posts

Posted - 2011-09-30 : 11:45:41

@visakh16 - It's probably overkill to use restore but all I want to do is really replace the entire row in that table to a previous version (i.e. from the test server) So something like UPDATE [PRODserver][dbo][table] WITH [TESTserver][dbo][table] WHERE userID='ABC' But I don't I have the right sytax to be able to get it to pick up the different server. How do I link them? and if I do link the two will they conflict with each as they both have the exact same tables?

@Cindyaz - Do you have a sample query that I can use that does this?

Your help is much appreciated.

Thanks

Chirag

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 11:51:55

quote:
Originally posted by Chirag23

@visakh16 - It's probably overkill to use restore but all I want to do is really replace the entire row in that table to a previous version (i.e. from the test server) So something like UPDATE [PRODserver][dbo][table] WITH [TESTserver][dbo][table] WHERE userID='ABC' But I don't I have the right sytax to be able to get it to pick up the different server. How do I link them? and if I do link the two will they conflict with each as they both have the exact same tables?

@Cindyaz - Do you have a sample query that I can use that does this?

Your help is much appreciated.

Thanks

Chirag

if you know the value of key column to be replaced you can acieve it by means of DELETE & INSERT operation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chirag23
Starting Member

13 Posts

Posted - 2011-09-30 : 11:57:14

Sorry I'm not to sure what you mean by that? I'm new to SQL apologies if I have posted this in the wrong place. I was hoping to get a example of what type of query I should be putting in to replace that particular row.

Chirag

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:06:55

you can do like

DELETE FROM ProdTable WHERE Key=<your key value>

and then use insert to insert new value

INSERT INTO ProdTable
SELECT columns
FROM DevTable
WHERE Key = <your key value>

also see if there's any fk to table before you start deletion.
In case fk is there you need to do insertion first, do remapping and then do the deletion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chirag23
Starting Member

13 Posts

Posted - 2011-09-30 : 12:20:35

I see what you mean however, within this table I simply want to replace the entire row which includes the key as I think there are other fields in that row that may determine what that key is. Also with the query you suggest above, how to do I INSERT from the other server?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:25:45

provided you've linked server set up with devserver with name DEV for example you can use like


INSERT INTO ProdTable
SELECT columns...
FROM [DEV].DevDBName.dbo.DevTable

see below to setup linked server

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources