| Author |
Topic |
|
vule
Starting Member
11 Posts |
 Posted - 2004-05-12 : 13:01:02
|
| Hi folks, I have set up two SQL servers on Network. Both of Servers are working. I would like to copy only two rows of one table exactly from Server 1 to same table name of Server 2. I have use DTS to do it, but DTS is copied hold data of table. I don't want to copy hold table like that. Do you have any idea, please help me out?Thanks,vl |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-12 : 13:17:52
|
| You can use INSERT INTO/SELECT FROM with a linked server:INSERT INTO DestinationDB.dbo.Table1 (Column1, Column2)SELECT Column1, Column2FROM Server2.SourceDB.dbo.Table1WHERE Column1 = 'SomeCondition'Tara |
 |
|
|
vule
Starting Member
11 Posts |
Posted - 2004-05-12 : 13:50:08
|
| Tara, I nem with SQL database. I don't how to use linked server. Please show me steps to go through linked server to copy rows of table at local machine to another machine.Thanks,vl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-12 : 13:55:06
|
| In Enterprise Manager, go to your destination server, go to Security, then to Linked Servers. Right click on it and select new linked server. Name the linked server the name of the source server. Select SQL Server for the server type. Go to the second tab, select the third radio button or whatever you think would be best for your environment. Then run the query that I posted but referencing the linked server that you added. The query is to be run on the destination server. For more information, take a look at linked servers in SQL Server Books Online.But if it's just two rows, why not just manually INSERT them?Tara |
|
|
|
vule
Starting Member
11 Posts |
Posted - 2004-05-12 : 14:54:22
|
| Tara, Can we INSERT two rows into same table name of Destination Server and replace existing rows at this tablle? How can we use manually INSERT?Thanks,vl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-12 : 15:00:02
|
| To replace existing rows use UPDATE. Have you looked up INSERT in SQL Server Books Online?INSERT INTO Table1 (Column1, Column2)VALUES ('SomeValue', 'SomeOtherValue')Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-05-12 : 15:54:22
|
| or use the "query" option when building the DTS package, instead of copying the whole table, it will copy just the rows you s[pecify in the query. play around with it a little, you'll get it. |
|
|
|
vule
Starting Member
11 Posts |
Posted - 2004-05-12 : 15:56:40
|
| Tara, I would like to update only two rows (called that "_news", "anon") of "Turbine_user" table as Server 2 database (production machine) as same as Server 1 database (test machine) after I have changed something at Server 1. That so I would like to update. Do you have any best idea, please help?Thanks,vl |
|
|
|
bjrnet
Starting Member
3 Posts |
Posted - 2004-05-13 : 08:35:24
|
Hello folks. I work with Vu and would like to re-state the question he asked regarding use of MS SQL. What we have is:1) Two MS SQL databases on two different machines2) Both have same database structure3) One database is considered the 'production' database4) One database is considered the 'test' databaseWe want to be able to use the 'test' database to test and configure some specific table entries before applying them to the 'production' database -- which has similar entries.What is the best 'automated' way to update a 'production' database with entries from a 'test' database? Essentially, we are attempting to 'REPLACE' an existing row in the 'production' database with an updated row in the test database. Both rows have the same key value. We need an automated way because this update will be a regular thing to do.Any help will be appreciated. We are not SQL experts.  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 11:50:32
|
| The best way is to use BACKUP/RESTORE. So backup the production database, copy the .BAK file over to your test server, then RESTORE it on the test server.Tara |
|
|
|
bjrnet
Starting Member
3 Posts |
Posted - 2004-05-13 : 15:16:00
|
| A backup / restore is not possible. We have a production database that is "in use". Let's say hundreds if not thousands of users are constantly using it. New users are being created and the database is constantly being updated by a web application.We want to update specific table entries in the database from another machine without having to take down the database each time we do it -- independent of the main application. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 15:17:59
|
| You don't have to take down the application in order to do a backup. BACKUP DATABASE is an online operation. It barely even affects performance.Tara |
|
|
|
bjrnet
Starting Member
3 Posts |
Posted - 2004-05-13 : 15:29:51
|
| OK, let's say I backup the database and make changes to it.When I restore it I will overwrite the existing database which now may be different. I will lose any changes made to it since the last backup. ????Am I missing something here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 16:20:00
|
| Yes that is true, but that's typically fine for a test environment but maybe in your environment it's not. Have you looked into replication?Tara |
|
|
|
|
Copy data from SQL server 1 to SQL Server 2?