compare count(*) of two tables and generate a command if the count(*) is different

Hello,

I search how I can compare count(*) of two tables and generate insert command if the count(*) is different.

Thanks
bibi92Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
A IF statement will do that:
IF (select COUNT(1) from Table1) <> (select COUNT(1) from Table2)
	INSERT INTO ....
ELSE
	PRINT 'Equal number of records'

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
Without knowing specifics about how your tables are structures, this would be the general idea.
IF (select count(*) from <target table>) <> (select count(*) from <source table>)
insert into <target table>
(<field list>)
select <field list>
from <source table>
where not exists (select 1 from <target table> where <target table>.<PK column> = <source table>.<PK column>)

Open in new window

0
 
bibi92Author Commented:
Ok, thanks is it possible to replace ELSE PRINT 'Equal number of records' by go to the next step of a job.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure. I just put there something so you could see that you can add a different operation in case of the values being equal.
0
 
bibi92Author Commented:
I have tested :
IF (select COUNT(1) from Table1) <> (select COUNT(1) from Table2)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Try this

IF ((select COUNT(1) from Table1) <> (select COUNT(1) from Table2))
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post all code?
0
 
bibi92Author Commented:
IF ((select COUNT(1) from LL.NX_TEST) <> (select COUNT(1) from FT_TEST..LL.NX_TEST))

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only that?
What about the INSERT statement?
0
 
bibi92Author Commented:
Insert statement contains 300 columns. I have added       BEGIN TRAN
GO
INSERT INTO and error syntax is resolved.

Regards
0
All Courses

From novice to tech pro — start learning today.