2 tables to be in sync, need to remediate

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

2 tables to be in sync, need to remediate

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-11-02 : 21:36:03

At the moment, we have 2 tables, almost exactly the same, in 2 diff db's. I need to have A update B. Table A that are not in Table B, and can update if needed. I thank you for any kind help.

1. I would like to make them having the same exact rows first.
what is the best way to do this. They are currently supporting 2 diff apps and we are going to move to one table but a priority at the moment is to setup a demo site.

The structure for Table A.is this:
UserId (PK, int, notnull) ***KEY
UserName (Varchar(50) notnull)
Password (Varchar(50) notnull)
Fullname (Varchar(100) null)
reports (Varchar(50) null)

Table B.
UserName (Varchar(50) notnull)
Password (Varchar(50) notnull)
Fullname (Varchar(50) null)
reports (Varchar(50) null)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-02 : 21:44:16

make username unique.

put a trigger on A to insert new records to B

put a trigger on A to update B when A is updated.

to initially synch 'em, do this:

UPDATE	b
SET	Password = a.Password,
	Fullname = a.Fullname,
	reports = a.reports
FROM	TableA a
JOIN	TableB b
On	a.username = b.username;
GO

INSERT	TableB
SELECT	UserName, Password, Fullname, reports
FROM	TableA a
LEFT JOIN
	TableB b
On	a.username = b.username
WHERE	b.username is NULL;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources