Write individual data from one database to another

[hapax]

I have some tables in Database A that I need to write to Database B, according to the following mapping:

LOAN.Amount -- writes to --> Asset.Balance
LOAN.Type -- writes to --> AssetNote.Note
LOAN.LienHolderName -- writes to --> Asset.ClientId
CUSTOMER.Id -- writes to --> Client.ClientId

etc. (many more...)

The databases are on the same SQL Server.

What's the best way to do this? SQL? or SSIS maybe?
If SQL, please give me example query.

Much thanks

 

[gmmastros]

First, you need to make sure you log in to Query Analyzer or SQL Server Management Studio with a login that has security permissions to both databases. Then...


[tt][blue]
Insert Into DatabaseTo.dbo.ToTableName(Col1, col2, col3)
Select ColA, ColB, ColX
From DatabaseFrom.dbo.FromTableName
[/blue][/tt]

Now, it appears as though you want to insert in to at least 3 different tables. You can only insert in to 1 table at a time, so you will need to write a separate insert statement for each table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

If they are on the same server, you don't NEED to set up anything special. Without knowing more, it could be as simple as:

UPDATE a
SET a.Column1 = b.Col1,
a.Column2 = b.Col2
FROM SomeDatabase.SomeSchema.SomeTable a
INNER JOIN SomeOtherDatabase.SomeSchema.SomeOtherTable b
ON a.JoinColumn = b.JoinColumn


INSERT INTO SomeDatabase.SomeSchema.SomeTable
(Column1, Column2, Column2)
SELECT Col1, Col2, Col3
FROM SomeOtherDatabase.SomeOtherSchema.SomeOtherTable a
LEFT OUTER JOIN SomeDatabase.SomeSchema.SomeTable b
ON a.JoinColumn = b.JoinColumn
WHERE b.SomeNonNullColumn IS NULL

Write your queries and schedule it as a job if you like.