Make an UPDATE with the value of another database

Asked

Viewed 3,234 times

1

My bank is like this:

cd_endereco  | ds_endereco
1            | Rua joão e maria
2            | Rua Manuel Silva

And in the other database, I have the same columns, while cd_cliente. I want to pass the values of this table to the client.

My code is like this:

"UPDATE tb_cliente SET ds_endereco=(
SELECT ds_endereco FROM tb_enderecos WHERE cd_endereco='"+codigoEndereco+"'
) WHERE cd_cliente=30"

NOTE: Line breaks do not exist, it is just to facilitate reading here on the site

c# sql

  • I think I don’t quite understand your question yet. Please give the structure of the two databases. You want to keep the same cd_client on both sides?

3 answers

1

If the other database is in the same instance as the SQL server:

    SELECT ds_endereco
    FROM [OutroBD].[schema].tb_enderecos
    WHERE cd_endereco = ...

If you are on another instance or another server - which must necessarily be linked to this one, you will need to use .. after the server identification:

    SELECT ds_endereco
    FROM [OutroBD]..[schema].tb_enderecos
    WHERE cd_endereco = ...

  • I still don’t have much experience with sql commands, sorry, but should I replace only select or this complete code? the name of the other table is tb_client!

1


If your version of SQL Server is higher than 2008, you have access to the clause MERGE.

When you want to keep information synchronized between two tables (and 2 databases), it is excellent. With it, you arrow one origin and fate, and instructed what the bank should do for each line in the origin. Example:

  • If it exists at source and destination, refreshes the destination
  • If it does not exist in the destination, enter the record
  • If it does not exist at source, remove it from the target table (probably the record was removed at source).

Example of how it would look:

 MERGE bancoDadosCliente..tb_cliente as target
 USING (SELECT cd_cliente, ds_endereco FROM bancoDadosOrigem..tb_cliente) AS source
 ON (target.cd_cliente = source.cd_cliente)
 WHEN MATCHED THEN 
    UPDATE SET ds_endereco = source.ds_endereco
 WHEN NOT MATCHED BY TARGET THEN
    THEN DELETE
 WHEN NOT MATCHED BY SOURCE THEN
    INSERT (cd_cliente, ds_endereco)
    VALUES (source.cd_cliente, source.ds_endereco)

See references: http://msdn.microsoft.com/en-us/library/bb510625%28v=sql.100%29.aspx

EDITION

If you want simpler commands, you can use simple INSERTs and UPDATEs, but will have to make a stricter control on no. Examples:

To update records that in the other bank that already have one cd_cliente registered

UPDATE d
SET
  d = ds_endereco -- aqui voce coloca uma lista das colunas a atualizar
FROM dbDestino..cd_cliente d
INNER JOIN dbOrigem..cd_cliente c
WHERE d.cd_cliente = c.cd_cliente

To enter records that do not exist in the destination:

INERT INTO dbDestino..cd_cliente (cd_cliente, ds_endereco)
SELECT c.cd_cliente, c.ds_endereco
FROM dbDestino..cd_cliente d
OUTER JOIN dbOrigem..cd_cliente c ON d.cd_cliente = c.cd_cliente
WHERE d.cd_cliente IS NULL -- pega registros que não existem no destino e insere

Deletes records that should not exist (deleted at source:

DELETE FROM dbDestino..cd_cliente
WHERE cd_client NOT IN (
   SELECT cd_cliente from dbOrigem..cd_cliente)

  • Thanks Rodrigo, but as I said I am layman in sql commands, I believe there is another way simpler than this, what the wrong in my command?

  • @Eduardof.Santos Do you intend to do this only once? Or will it be an ongoing process?

  • Every time the Method is called!

  • 1

    I believe you should take a look at this merge clause then. If you try to understand part by part, you will see that it is not that difficult. You can use a dozen other commands separately, but I think it’s worth understanding better how this works.

  • I understand, I will explore it, it seems to have very useful property, but for now, I need something simple, take the value of one table and update the other.

  • 1

    @Eduardof.Santos will give you some simple commands updating the answer then.

  • Okay, thanks, Ta helping a lot

  • Let’s go continue this discussion in chat.

  • Right, in my case is the UPDATE, those (..) that Oce put, means the conditions? cd_client, ds_addressee?

  • No, it’s the schema... the tables are referenced by [Server]. [Schema]. [Nomatable]... Usually the default schema is the dbo, then you don’t even remember it exists. But when you want to access another bank you need to pass this name. If you are already logged in the scheme dbo (same location where the destination table is), you do not need to write the name, but need to indicate that you passed through the scheme... so dbDestino.{dbo subentendido}.tb_cliente

  • I thought this command would be much simpler, I’ll see here if I do this SELECT outside of UPTATE, which returns this value as string, very complex these commands, I thought the logic was simpler, just as I was trying to do anyway vlw!

Show 6 more comments

0

I did not get the answer I wanted, I was able to solve the problem in another way, making SELECT out of UPDATE, I solved the PROBLEM, but not DOUBT!

Browser other questions tagged c# sql

You are not signed in. Login or sign up in order to post.