Solved

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

Posted on 2014-09-03
10
89 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 47

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 47

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 47

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 47

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 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