INSERT INTO where if a Select statement returns no records use an alternate one

Question

Post reply

INSERT INTO where if a Select statement returns no records use an alternate one

Sean Grebey

Ten Centuries

Points: 1159
More actions
March 11, 2016 at 8:00 am

#305625

I feel like this is pretty basic but I have never done it before. How can I do an INSERT INTO and do some kind of conditional where if the select statement returns zero records use an alternate one?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 1

Sean Grebey

Ten Centuries

Points: 1159

March 11, 2016 at 8:11 am

#1864586

Union maybe?

Phil Parkin SSC Guru Points: 246979 More actions · Answer 2

Perhaps you could do the INSERT followed by

DECLARE @RwCount INT = @@ROWCOUNT

IF @RwCount = 0

BEGIN

--Insert here

END

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

UNION will get you multiple rows if the primary one hits and the alternate does also.

At first blush I can't think of a way to do this in a single pass. You need to do the first insert and test for @@ROWCOUNT = 0 (watch out if you have trigger(s) on the table being insert into though). Then fire the second if no rows affected.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 4

TheSQLGuru (3/11/2016)
UNION will get you multiple rows if the primary one hits and the alternate does also.
At first blush I can't think of a way to do this in a single pass. You need to do the first insert and test for @@ROWCOUNT = 0 (watch out if you have trigger(s) on the table being insert into though). Then fire the second if no rows affected.

Can I do the union and do a Top 1 on the Union or is that just bad practice?

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 5

Phil Parkin (3/11/2016)
Perhaps you could do the INSERT followed by
DECLARE @RwCount INT = @@ROWCOUNT
IF @RwCount = 0
BEGIN
--Insert here
END

Thanks

drew.allen SSC Guru Points: 76996 More actions · Answer 6

Something like the following:

-- do your first insert

INSERT ....

--conditionally do the second insert

IF @@ROWCOUNT = 0

INSERT ....

Here is the info about @@ROWCOUNT

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 7

Sean Grebey

Ten Centuries

Points: 1159

March 11, 2016 at 8:38 am

#1864601

@@ROWCOUNT did it thanks for the help guys