Transferring Data - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Transferring Data

Author

Topic

compren
Starting Member

1 Post

Posted - 2010-08-06 : 09:18:47

I have 2 databases with 2 tables of the same name. One is updated with username,password,email and the other is not. How do i copy the 3 columns from that one table to the other table in the other database? Both tables have a field named SSN, so each entry is unique.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-17 : 10:03:31

cross database query using the 3 part name and join them on the relevant column

database_name.schema_name.object_name

--------------------
keeping it simple...

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 12:40:23

on what premises do you want to copy 3 columns from one to another table?

please describe your data flow.

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 06:00:27

[code]
UPDATE D
SET username = S.username,
password = S.password,
email = S.email
FROM dbo.MyTable AS D -- Destination
JOIN MyOtherDatabase.dbo.MyTable AS S -- Source
ON S.SSN = D.SSN
WHERE (S.username <> D.username OR (S.username IS NULL AND D.username IS NOT NULL) OR (S.username IS NOT NULL AND D.username IS NULL))
OR (S.password <> D.password OR (S.password IS NULL AND D.password IS NOT NULL) OR (S.password IS NOT NULL AND D.password IS NULL))
OR (S.email <> D.email OR (S.email IS NULL AND D.email IS NOT NULL) OR (S.email IS NOT NULL AND D.email IS NULL))
[/code]
FWIW we put "COLLATE Latin1_General_BIN2" after the <> test on Varchar columns - to force them to be case sensitive - i.e. we want to update ALL differences, even if just case. For example:
[code]
OR (S.email <> D.email COLLATE Latin1_General_BIN2 OR ..
[/code]
This will update ALL rows that are different. Make sure this is what you want to do before you run it! and take a backup.

You can change the UPDATE / SET to be

SELECT COUNT(*)

to see how many rows will update, or a SELECT statement showing values from Source/Destination tables side-by-side for checking etc.

Also, you can put

BEGIN TRANSACTION

before the update statement. Run it. Decide if the number of rows was reasonable (or run SELECT statements on Destination table to make further checks [of the newly-changed data] and then execute a COMMIT statement, and if not execute a ROLLBACK instead. Note that you will be blocking other users until you execute either COMMIT or ROLLBACK.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources