Count affected rows after SQL Query Statement

In database instead of COUNT() method in SQL SELECT query statement we can use ROWCOUNT_BIG() after simple SQL SELECT query statement.ROWCOUNT_BIG() function operates like @@ROWCOUNT and returns the number of rows affected by the last statement executed.However, the return type of data type is bigint.

SELECT statement, this function returns the number of rows returned by the SELECT statement.

INSERT, UPDATE, or DELETE statement, this function returns the number of rows affected by the data modification statement.

Syntax -

ROWCOUNT_BIG()

Examples -
SELECT * FROM Table_Name
SELECT ROWCOUNT_BIG()

@@ROWCOUNT

USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee SET JobTitle = N'Executive' WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

It returns total number of count of SQL Statement.

Advantage -

If the number of rows returns more than 2 billion, use ROWCOUNT_BIG.

Because it returns Bigint data type which can store larger range of numbers than the int data type.

Copy a table from one database to another in SQL Server 2005/2008

If you have a table in a database and you would like to copy the table to another database, use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

How to copy columns from one table into another table

Example:

INSERT INTO staff( firstName, lastName, address, homePhone, cellPhone, latitude, longitude )
SELECT firstName, lastName, address, homePhone, cellPhone, latitude, longitude
FROM gfxContact