Compare 2 tables

Transact-SQL
1

Hi everyone,

I want to write a query that return only value after the last one on table
I have 2 tables with same fields but Table A has more data than Table B.

I want to write a query that returns the data that not in table B. there is a lot of rows on both table.
and there is a field with incremental value as Key value on both tables.

can anyone help me with this?
I'm not good with queries

2

What is the common column in both tables?

1 Like
3

Hi ahmeds08
there is a column with numbers from 1 ... etc.
can i use this column to know what rows aren't in table B?
it will be the rows after the last number of this column in table B

4

That will not guarantee data accuracy, it is just an auto incremented number.
you need some column which exists in both the tables with actual data.

1 Like
5

yes, this column is accurate.
as the number increased when new row added.

6

SELECT Columnname FROM table1
WHERE Columnname NOT IN
(SELECT Columnname FROM table2)

1 Like
7

thanks ahmed for the query
but when i try it, there were no data in return
did I miss something here?

8

post the table structure

1 Like
9

SELECT * FROM logicI.dbo.ACCOUNTENTRY
WHERE recid NOT IN
(SELECT recid FROM DLive.dbo.ACCOUNTENTRY)

10

those are 2 sprate database in same sql server

12

logicI.dbo.ACCOUNTENTRY has more records?

can you check the counts
SELECT count(recid) FROM logicI.dbo.ACCOUNTENTRY

SELECT count(recid) FROM DLive.dbo.ACCOUNTENTRY)

1 Like
13

count from logic is 227579
count from DLive is 515343

14

it should be

SELECT * FROM DLive.dbo.ACCOUNTENTRY
WHERE recid NOT IN
(SELECT recid FROM logicI.dbo.ACCOUNTENTRY)

1 Like
15

thanks ahmeds for your reply
i tried this query.

INSERT INTO logicI.dbo.ACCOUNTENTRY(ACCOUNTINGCURRENCYAMOUNT,LEDGERACCOUNT,RECID)
SELECT * FROM logicI.dbo.ACCOUNTENTRY
WHERE recid NOT IN
(SELECT recid FROM DLive.dbo.ACCOUNTENTRY)

but I got (0 row(s) affected)

could you please help with this?

16

Always mention column list and avoid using select *
can you try this

INSERT INTO logicI.dbo.ACCOUNTENTRY(ACCOUNTINGCURRENCYAMOUNT,LEDGERACCOUNT,RECID)
SELECT ACCOUNTINGCURRENCYAMOUNT,LEDGERACCOUNT,RECID FROM DLive.dbo.ACCOUNTENTRY
WHERE recid NOT IN
(SELECT recid FROM logicI.dbo.ACCOUNTENTRY )

1 Like
17

thanks a lot ahmed,
this was really helpful and did exactly what I wanted,
just one more thing,
can I use nolock with this query? could you show me please?
just to be safe.
thanks again

18

you are welcome

INSERT INTO logicI.dbo.ACCOUNTENTRY(ACCOUNTINGCURRENCYAMOUNT,LEDGERACCOUNT,RECID)
SELECT ACCOUNTINGCURRENCYAMOUNT,LEDGERACCOUNT,RECID FROM DLive.dbo.ACCOUNTENTRY WITH (NOLOCK)
WHERE recid NOT IN
(SELECT recid FROM logicI.dbo.ACCOUNTENTRY WITH(NOLOCK))

2 Likes
19

thanks a lot again.
you were really helpful

1 Like