Solved

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

Posted on 2014-09-03
10
91 Views
Last Modified: 2014-09-03
Hello,

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

Thanks
0
Comment
Question by:bibi92
10 Comments
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40300925
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
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40300926
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
 

Author Comment

by:bibi92
ID: 40300942
Ok, thanks is it possible to replace ELSE PRINT 'Equal number of records' by go to the next step of a job.
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40300956
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
 

Author Comment

by:bibi92
ID: 40300983
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
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40300995
Hi,

Try this

IF ((select COUNT(1) from Table1) <> (select COUNT(1) from Table2))
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40301006
Can you post all code?
0
 

Author Comment

by:bibi92
ID: 40301029
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40301049
Only that?
What about the INSERT statement?
0
 

Author Comment

by:bibi92
ID: 40301066
Insert statement contains 300 columns. I have added       BEGIN TRAN
GO
INSERT INTO and error syntax is resolved.

Regards
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question