Post reply

Using COUNT to capture the number of inserted records

  • March 5, 2012 at 5:37 pm

    #254183

    Hello All,

    Is there any way to simplify this is bit. Can I avoid declaring the @Inserts table variable and instead set @InsertCount directly from the virtual inserted table?

    CREATE TABLE #Test(TestID int)

    DECLARE @Inserts TABLE (TestID int)

    DECLARE @InsertCount int

    INSERT INTO #Test

    OUTPUT inserted.TestID INTO @Inserts

    SELECT 1 UNION SELECT 2

    SET @InsertCount = (SELECT COUNT(TestID) FROM @Inserts)

    PRINT @InsertCount

    SELECT * FROM #Test

    DROP TABLE #Test

  • March 5, 2012 at 9:00 pm

    #1455093

    This:

    CREATE TABLE #Test(TestID int)

    DECLARE @Inserts TABLE (TestID int)

    DECLARE @InsertCount int

    INSERT INTO #Test

    OUTPUT inserted.TestID INTO @Inserts

    SELECT 1 UNION SELECT 2

    SET @InsertCount = @@ROWCOUNT;

    PRINT @InsertCount

  • March 6, 2012 at 8:03 am

    #1455338

    Thanks. It seems the lesson here is if you just need the count, use @@ROWCOUNT. If you want to do something with the details of the inserted records, then use the output clause.

Viewing 3 posts - 1 through 2 (of 2 total)

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