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?

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.

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)

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

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)

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)

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 )

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))

19

thanks a lot again.
you were really helpful