SQL - Insert if the number of rows is greater than

Question

I have created a SQL query that will return rows from an Oracle linked server. The query works fine and will return 40 rows, for example. I would like the results to only be inserted into a table if the number of rows returned is greater than 40.

My thinking would then be that I could create a trigger to fire out an email to say the number has been breached.

If I were you, I'd store the output of the linked server query in a temp table. You can then use an `IF` statement with a `@@ROWCOUNT` to determine whether or not the threshold is met. If so, `INSERT` the records to a real table, and send a mail. No need to use triggers. — SchmitzIT, Mar 11 '14 at 12:54
You could store the row count in a variable and use it with an IF condition to conditionally insert. — shree.pat18, Mar 11 '14 at 12:58

score 3 · Accepted Answer · edited Mar 11 '14 at 13:23

3

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM LinkedServer.database.schemaname.tablename

IF @cnt > 40

INSERT INTO table1 VALUES(col1, col2, col3 .....)

edited Mar 11 '14 at 13:23

answered Mar 11 '14 at 13:00

Kalyan

56
2

1

This seemed like the easiest way to do it, thank you for your help – stevenk Mar 13 '14 at 16:13

score 1 · Answer 2 · answered Mar 11 '14 at 13:01

Let's say that the query is:

select a.*
from remote_table a

Now you can modify the query:

select a.*, count(*) over () as cnt
from remote_table a

and will contain the number of rows.

Next,

select * 
from (
select a.*, count(*) over () as cnt
from remote_table a
)
where cnt > 40;

will return only if the number of rows is greater than 40.

All you have to do is

insert into your_table
select columns 
from (
select columns, count(*) over () as cnt
from remote_table a
)
where cnt > 40;

and will insert only if you have more than 40 rows in the source.

score 0 · Answer 3 · answered Mar 11 '14 at 12:57

Create Procedure in sqlserver and use count() function for conditional checking for row count or use @@ROWCOUNT.

if ((select count(*) from Oraclelinkservertable) > 40)
begin
-- code for inserting in your table
Insert into tablename
select * from Oraclelinkservertable
end

score 0 · Answer 4 · answered Mar 11 '14 at 13:02

0

Try using OFFSET.

SELECT * FROM tableName ORDER BY colName OFFSET 40 ROWS

answered Mar 11 '14 at 13:02

Arjit

3,290
1
17
18

SQL - Insert if the number of rows is greater than

4 Answers4

Linked