Post reply

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

  • 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?

  • March 11, 2016 at 8:11 am

    #1864586

    Union maybe?

  • March 11, 2016 at 8:18 am

    #1864589

    Perhaps you could do the INSERT followed by

    DECLARE @RwCount INT = @@ROWCOUNT

    IF @RwCount = 0

    BEGIN

    --Insert here

    END

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • March 11, 2016 at 8:19 am

    #1864591

    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

  • March 11, 2016 at 8:23 am

    #1864595

    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?

  • March 11, 2016 at 8:24 am

    #1864596

    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

  • March 11, 2016 at 8:26 am

    #1864597

    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

  • March 11, 2016 at 8:38 am

    #1864601

    @@ROWCOUNT did it thanks for the help guys

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

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