How do we restore just few columns of a table from one database to another.. say for example, if we need only 3 attribute's data of the a table consisting of 30 attributes in total. It is a sql server 200O database. Anything that we need to keep in mind in order to process this ? my initial idea was, to use export and import utility, to get the specific columns from table in the DB...This question is to confirm my thoughts and learn if any new ideas or efficient ways to do this.
Thanks!
If this is a one-time operation, your idea should work fine. If you need to maintain the copy, you can use replication with only the desired columns selected as articles. Please note I have only used replication with SQL Server 2005, but I believe what I described should also work with 2000.
If you want a neat GUI to handle the process then RedGate's SQL Data Compare is a great tool. I use it when we have to do a RESTORE and then identify what has changed since the last backup but it could do what you need too. There is a 14 day free trial if you want to take a look at it http://www.red-gate.com/products/SQL_Data_Compare/index.htm, as they say, it compares and synchronises database contents. Check out their SQL Compare too as that could help you synch the schemas/tables etc in the two databases.
You can restore specific columns from a backup table using an update statement and sub select.
i.e. An answer/example is here:
https://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server
i.e.
you can join both tables even on UPDATE statements,
e.g.
<code>UPDATE a
SET a.marks = b.marks
FROM tempDataView a
INNERJOIN tempData b
ON a.Name = b.Name
for faster performance, define an INDEX on column marks on both tables.
using SUBQUERY
<code>UPDATE tempDataView
SET marks =(SELECT marks
FROM tempData b
WHERE tempDataView.Name = b.Name
)
2 People are following this question.
