Verify migrated records in SQL Server tables

user_12947035 0
Viewed 43

I have a scenario where I transferred data from table t1 to t2. Not every record was transferred. I want to figure out which IDs were completely transferred. For example, I have a subset of data in which both IDs 23 and 25 has 5 records each with different section and subsections. Since only 2 records from ID 23 were transferred to t2, I do not want them in my resultant query. Whereas, for ID 25, all the records were transferred to t2. So, I want it to reflect in my result.

I got this far

select * 
from t2
where exists (select * from t1 
              where t1.id = t2.id 
                and t1.section = t2.section
                and t1.sub = t2.sub
              group by id)

Table 1

id  section sub
----------------
23  1   9
23  1   10
23  2   2
23  3   2
23  3   3
24  1   9
24  1   10
24  2   2
24  3   2
24  3   3
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

Table 2

id  section sub
----------------
23  1   9
23  1   10
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

Required result:

id  section sub
---------------
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

Code to create tables

CREATE TABLE t1 
(
    id varchar(3),
    section varchar(4), 
    sub varchar(2)
)

CREATE TABLE t2 
(
    id varchar(3),
    section varchar(4), 
    sub varchar(2)
)

INSERT INTO t1 (id, section, sub)
VALUES ('23', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '3', '3')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '3', '3')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '3', '3')


INSERT INTO t2 (id, section, sub)
VALUES ('23', '1', '9')
INSERT INTO t2 (id, section, sub)
VALUES ('23', '1', '10')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '1', '9')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '1', '10')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '2', '2')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '3', '2')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '3', '3')
sql-server mysql sql
1 Answers

You can try the following, just need to count the number of each ID in t2 and compare that to the same count in t1 where they are the same:

select id,section,sub from (
    select *, Count(*) over(partition by id) c
    from t2
)t
where c=(
    select Count(*)
    from t1
    where t1.id=t.id
    group by t1.id
)
user_153326500
Related