Verifying if previous registration already exists (SQL server 2008 r2)

Question

Verifying if previous registration already exists (SQL server 2008 r2)

Navigation

#1 by (1 votes)

2

I have the following script with more than 20,000 records in each table:

With registration dates from January to February 2018:

INSERT Table_que_usare (Usuario, IDUnica, servidor)
select Usuario, IDUnica, servidor
from table1

With registration dates from March to May 2018:

INSERT Table_que_usare (Usuario, IDUnica, servidor)
select Usuario, IDUnica, servidor
from table2

With registration dates from August to December 2018:

INSERT Table_que_usare (Usuario, IDUnica, servidor)
select Usuario, IDUnica, servidor
from table3

In each database there is more than one user repeated, I want to know if there is any way to make the selection that checks me if the user does not exist, then insert the data and those that do not exist are all inserted to the exclusion of the repeated ones.

NOTE: Take into account that the databases come from more than 3000 SQL Server 2008 R2 backups.

sql sql-server

asked by Juan Carlos Villamizar Alvarez 07.08.2018 в 00:06

source

1 answer

How can I do a try catch and repeat the option I am doing? Maintain the session when downloading the capcha

score 1 · Answer 1

The most optimal would be in this way

DECLARE @Tope numeric(18,0)
DECLARE @Ciclo numeric(18,0)
SET @Tope = 0
SET @Ciclo = 0

SELECT @Ciclo=MIN(IDUnica) FROM table1 
SELECT @Tope=MAX(IDUnica) FROM table1 
WHILE @Ciclo<=@Tope
BEGIN
    IF NOT EXISTS(SELECT usuario FROM Table_que_usare  where usuario IN (SELECT usuario FROM table1 where IDUnica=@Ciclo))
        INSERT Table_que_usare (Usuario, IDUnica, servidor)
        SELECT Usuario, IDUnica, servidor
        FROM table1
END

Assuming that the IDUnica field is numeric, otherwise you would have to create a table type variable to insert the whole table again (table1, table2 and table3) and add a numeric field that you could call "sequence" and then you insert the data preceded with a row_number() OVER (ORDER BY IDUnica) and then you would use that variable type table in the previous script