This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

Using @@rowcount and other global variables Part two of a two-part series of blogs
SQL comes complete with a few useful global variables, giving you information such as the row number of the record just inserted, or the number of rows affected by a query. Here's how to use them! Global Variables in SQL Getting the number of rows affected - @@ROWCOUNT (this blog) This is part of our full online SQL tutorial blog. However, you might also like to consider booking onto one of our SQL courses.

Using @@rowcount and other global variables

Part two of a two-part series of blogs

SQL comes complete with a few useful global variables, giving you information such as the row number of the record just inserted, or the number of rows affected by a query. Here's how to use them!

Global Variables in SQL
Getting the number of rows affected - @@ROWCOUNT (this blog)

This is part of our full online SQL tutorial blog. However, you might also like to consider booking onto one of our SQL courses.

Posted by Andy Brown on 28 January 2013

Getting the number of rows affected - @@ROWCOUNT

If you've just selected, inserted, deleted or updated rows in SQL, @@rowcount will tell you how many. However, you have to access it immediately!

@@ROWCOUNT keeps getting overwritten

Here are a couple of examples: one which will work, and one which won't. Let's start with the one which works. The following SQL takes all films winning 10 or more Oscars, and puts them in a new table:

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@@rowcount AS varchar(3)) +

' rows in new table'

Here's the output I got when I just ran this query:

The query put two rows in the new table.

This works because I interrogated the row count immediately. If you add in another statement, however, things go awry:

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

-- any command here resets row count

PRINT 'Output follows'

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@@rowcount AS varchar(3)) +

' rows in new table'

This would show:

This time, the query would show that no rows had been added.

The reason is that @@rowcount holds the number of rows affected by the last executed statement - which in the above example is 0, because the PRINT command doesn't affect rows in a table!

Using a variable to store @@rowcount

An easy way round this is to store the number of rows affected, then use this subsequently:

-- will hold number of rows added

DECLARE @NumRowsAdded int

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

-- store number rows inserted

SET @NumRowsAdded = @@ROWCOUNT

-- any command here resets row count

PRINT 'Output follows'

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@NumRowsAdded AS varchar(3)) +

' rows in new table'

Here the use of the @NumRowsAdded variable gets round the problem.

Parts of this blog
Global Variables in SQL Getting the number of rows affected - @@ROWCOUNT (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Getting the number of rows affected - @@ROWCOUNT

@@ROWCOUNT keeps getting overwritten

Using a variable to store @@rowcount

Head office

London

Manchester