Vinicius Nascimento Vinicius Nascimento - 9 months ago 25
SQL Question

Insert into same table with different code_store

I have two stores, store 1 and 2, store 1 is closed and a need to insert all his clients into the same table but with code_store being 2 now. The structure of the table is

Code_clients_store (PK) code_clients code_store
1 35 1

I need to do something like this:

2 35 2

Insert the same client with code_store different, but don't delete the old client with store 1.

I'm trying something like this

Insert into table t_clients_stores
from t_clients stores
where cod_store = 1
set cod_store = 2

But it's not working, someone know a better way to do this?

Answer Source
Insert into t_clients_stores (cod_store, code_clients)
select 2, s.code_clients
from t_clients stores s 
where s.cod_store = 1 

and not exists ( select 1 
                 from t_clients t 
                 where t.cod_store = 2 
                 and and t.code_clients = s.code_clients )

INSERT (Transact-SQL)