Need to count inserted rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to count inserted rows

Author

Topic

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-30 : 13:39:12

I need to count how many rows are inserted and then how many updated from a table then put that result in a new table.

Lisa Jefferson

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-30 : 13:49:27

Without a little more info, I may be waaaaay off base, however this may give you an idea.

DECLARE @NoInserted INT, @NoUpdated INT

INSERT INTO SomeTable <rest of statement>

SET @NoInserted = @@ROWCOUNT

UPDATE SomeTable <rest of statement>

SET @NoUpdated = @@ROWCOUNT

INSERT INTO NewTable (CountInserted, CountUpdated)
SELECT @NoInserted, @NoUpdated

--
Gail Shaw

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-30 : 16:38:30

This is my insert table. Data is coming from tbl_buffer_openorders and inserting new records into tbl_data_orders. How can I count the number of inserts?

Insert Into tbl_Data_Orders ( OABL, ODLOTSEQ, OACUSTPO, OABLDATE, OAREGN, OASHIPDTMM, OASHIPDTDD, OASHIPDTYY, OASHIPVIA,
OAFOB, OAATTN, COBILLNUM, COCNTRY, OASFNAME, OASFAD1, OASFAD2, OASFCITY, OASFST, OASFZIP,
OASFZIP2, OASTNAME, OASTAD1, OASTAD2, OASTCITY, OASTST, OASTZIP, OADESC1, OAOPID, ODPNUM,
PUPNAME1, PUPNAME2, ODQORD, ODMEAS, ODUM, ODCSC, OCCPREM1, OCCPREM2, OCCPREM3, OCCPREM4,
OCCPREM5, OCCPREM6, OCCPREM7, OCCPREM8, OCCPREM9, OCCPREM10, OCCPREM11, OCCPREM12, Date_Created, NumChanges)
Select OABL, ODLOTSEQ, OACUSTPO, OABLDATE, OAREGN, OASHIPDTMM, OASHIPDTDD, OASHIPDTYY, OASHIPVIA,
OAFOB, OAATTN, COBILLNUM, COCNTRY, OASFNAME, OASFAD1, OASFAD2, OASFCITY, OASFST, OASFZIP,
OASFZIP2, OASTNAME, OASTAD1, OASTAD2, OASTCITY, OASTST, OASTZIP, OADESC1, OAOPID, ODPNUM,
PUPNAME1, PUPNAME2, ODQORD, ODMEAS, ODUM, ODCSC, OCCPREM1, OCCPREM2, OCCPREM3, OCCPREM4,
OCCPREM5, OCCPREM6, OCCPREM7, OCCPREM8, OCCPREM9, OCCPREM10, OCCPREM11, OCCPREM12, Current_TimeStamp, 0
From tbl_Buffer_OpenOrders
WHERE NOT EXISTS (Select OABL, ODLOTSEQ, OACUSTPO, OABLDATE, OAREGN, OASHIPDTMM, OASHIPDTDD, OASHIPDTYY, OASHIPVIA,
OAFOB, OAATTN, COBILLNUM, COCNTRY, OASFNAME, OASFAD1, OASFAD2, OASFCITY, OASFST, OASFZIP,
OASFZIP2, OASTNAME, OASTAD1, OASTAD2, OASTCITY, OASTST, OASTZIP, OADESC1, OAOPID, ODPNUM,
PUPNAME1, PUPNAME2, ODQORD, ODMEAS, ODUM, ODCSC, OCCPREM1, OCCPREM2, OCCPREM3, OCCPREM4,
OCCPREM5, OCCPREM6, OCCPREM7, OCCPREM8, OCCPREM9, OCCPREM10, OCCPREM11, OCCPREM12
From tbl_Data_Orders
WHERE tbl_Buffer_OpenOrders.oabl = tbl_Data_Orders.oabl AND
tbl_Buffer_OpenOrders.ODLOTSEQ = tbl_Data_Orders.ODLOTSEQ)

Lisa Jefferson

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-30 : 19:19:21

Read about @@rowcount in SQL Server Books Online

CODO ERGO SUM

zalak
Starting Member

2 Posts

Posted - 2011-08-17 : 03:56:28

quote:
Originally posted by GilaMonster

Without a little more info, I may be waaaaay off base, however this may give you an idea.

DECLARE @NoInserted INT, @NoUpdated INT

INSERT INTO SomeTable <rest of statement>

SET @NoInserted = @@ROWCOUNT

UPDATE SomeTable <rest of statement>

SET @NoUpdated = @@ROWCOUNT

INSERT INTO NewTable (CountInserted, CountUpdated)
SELECT @NoInserted, @NoUpdated

--
Gail Shaw

zalak

zalak
Starting Member

2 Posts

Posted - 2011-08-17 : 03:58:37

this is not work....

zalak

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-17 : 08:58:20

Have a look at the output clause perhaps?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources