question

Katie 1 avatar image
Katie 1 asked

Restoring just few columns of a table.

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!

sql-server-2000databaserestoreimport-datadata
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

endizhupani avatar image
endizhupani answered
I suggest you check out [xSQL Data Compare][1] too. To this day, I am yet to find a comparison / synchronization task that it hasn't been able to complete. As for your problem, you can specify which columns you want to compare and synchronize in the mapping between each table pair. ![alt text][2] Disclosure: I'm affiliated to xSQL. [1]: http://www.xsql.com/products/sql_server_data_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql [2]: /storage/temp/3993-mapping.png

mapping.png (14.7 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AllanFord66 avatar image
AllanFord66 answered

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
        )
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.