How to update one table with the different elements of table1-table2

[RH+]

I have 2 databases.
Table in database 1 has 5 fields
Table in database 2 has 15 fields

I made 2 querys that retrieve the 5 fields from the first and 5 fields from the second that are the same from the first.

I need to update data in Table1 with elements present in table2 that arent in table1 without deleting elements in Table1.

Something like Table1=Table1-Table2 (so I update the table).

How can I do this?
thanks!

[Alsvha]

Well - without knowing much of the structure or the specifics; I'll just use some pseudo-code to illustrate the principle of updating a table using join:

You can do the following methodolgy for "complex" updates in SQL:

UPDATE T1 SET
 T1.Fields = T2.Field
FROM <Table1> T1
INNER JOIN <Table2> T2 ON T1.value = T2.value
WHERE <Some Where clause>

This means you can make your "update" as a select, and when you get selected what you want to update with, then you can use the update syntax I showed above.

It is about the best I can do given the information you have provided.

[RH+]

That's works when the tables are from the same DB, this are from different DBs, so I have 2 connection strings and I cant do that in only one query (I think).
The tables fields are: IDClient, Name,address,date1,date2
Please tell me if you need more information to understand what I'm trying to do.
thanks

[Alsvha]

Well - that depends on whether the DBs are on different servers, which arcitecture you want to use and so on.
For example if the databases are on the same server, you can simply use qualified names [database].[schema].[tablename] but if they're on different servers you either need to link the servers or pull the data into a code layer and then save the changes.

[RH+]

Databases are on different servers
And I need to be sure that the table with more records (its always the same DB) is Read Only.
So I need to copy the new items from that table to the other table.
I hope its clear now.

Thanks!
(And sorry for my English if Im using some word wrong)

[Alsvha]

Well - then you can either link the servers directly so they can see each other, or make a code-layer which does the copying for you.

I'd properly opt for making a function/application/code layer myself in that situation.

So select what you need out of server one, do what comparisons you need to do and save it on server two. Or just select the data from server one, dump into a temporary table on server to, and do the comparison directly in SQL on server two.

[RH+]

I like the temp table because this process is done 1 time each month, I think it will be better than go thought a datareader looking for elements from another datareader to do the inserts.
I'll try this when i get home later...
I think this will be the query (MS SQL)
Insert into Table1 (select * from TempTable where TempTable.ID NOT IN (Table1.ID))

thanks!