Update database A with Values from B? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Update database A with Values from B?

Author

Topic

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 16:01:57

I have two databases, database A and database B

Both have an identical table called "Codes" containing billing numbers and prices.

Database B is one I use for testing, and is out of date. Is there an easy way to load the values from Table_A.codes into Table_B.codes?

I know how to do an update from one table to another within the same database, but am clueless how to do it when the data reside in separate databases.

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-10 : 16:11:37

DELETE FROM DB1.dbo.Table1

INSERT INTO DB1.dbo.Table1
SELECT * FROM DB2.dbo.Table1

Tara Kizer
aka tduggan

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 17:28:35

Hmmm, so can I update the values this way instead?

Update DB1.dbo.Table1
Set a.Fee = b.Fee
From DB1.dbo.Table1 a, DB2.dbo.Table1 b
Where a.code=b.code

Would that work?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-10 : 17:35:50

For a test table, I prefer to just wipe out and start over. But here you go:

UPDATE t1
SET Column1 = t2.Column1
FROM DB1.dbo.Table1 t1
INNER DB2.dbo.Table1 t2
ON t1.JoinColumn = t2.JoinColumn

Tara Kizer
aka tduggan

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 18:00:13

Still not getting it....
I tried the following and I am getting errror messages
Here is the code I tried:

Tran
Update t1
Set Fee = t2.Fee
From Training.dbo.procedures t1
Inner CentricityPM.dbo.procedures t2
on t1.CPTCode = t2.CPTCode And t2.fee is not Null

and when I execute I get the following errors:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Tran'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '.'.

Any ideas where I am messing up.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-10 : 18:03:15

You'll need INNER JOIN instead of INNER, I had a typo in my last post.

Tara Kizer
aka tduggan

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 18:07:31

Hmmm, and I thought that was so cool that you could just type Inner for an Inner Join.

Off to give it a shot;

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 18:10:43

Getting closer....

I used:

Tran
Update t1
Set Fee = t2.Fee
From Training.dbo.procedures t1
Inner Join CentricityPM.dbo.procedures t2
on t1.CPTCode = t2.CPTCode And t2.fee is not Null

---Commit
Rollback

No I get just the error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Tran'.

Any ideas what I have messed up?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-10 : 18:12:01

Do you mean BEGIN TRAN? Not to mention COMMIT TRAN AND ROLLBACK TRAN...

Tara Kizer
aka tduggan

Conjurer
Starting Member

30 Posts

Posted - 2006-01-10 : 18:17:41

Yep that was it!
Needed Begin in front of the TRAN.

Got it to work, and they are all good to go.... seeing the light at the end of tunnel now!

Thanks so much for your help. That is something I am going to be able to use a lot so it is going to be saving me a lot of work.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-01-10 : 20:17:12

Conjurer....PLEASE get a good Transact SQL book if you are going to be doing a lot of coding.

Any book by Ken Henderson is good. Books Online is also a great reference, as I mentioned in my other post.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources