Solved

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

Posted on 2014-09-03
10
82 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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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 25

Expert Comment

by:Shaun Kline
Comment Utility
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
Comment Utility
Ok, thanks is it possible to replace ELSE PRINT 'Equal number of records' by go to the next step of a job.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 14

Expert Comment

by:Vikas Garg
Comment Utility
Hi,

Try this

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

Expert Comment

by:Vitor Montalvão
Comment Utility
Can you post all code?
0
 

Author Comment

by:bibi92
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Only that?
What about the INSERT statement?
0
 

Author Comment

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

Regards
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now