Before and After Table Update - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Before and After Table Update

Author

Topic

SLM09
Starting Member

31 Posts

Posted - 2009-10-01 : 13:35:43

Hey all,

I have written some SQL to update a table for me based on dates, and I am trying to have a simple Select when all is said and done display the row counts of Before and After the run as well as the difference.

Problem is, if I do the SELECT, run the update, and do another, the values are separate. What I THINK needs to be done is hold the count from before the update, run the update, then get that count and the new count.

Unfortunately it's the simple stuff that I always get stuck on. I tried declaring and int and using a SET x = select count... but that didn't work. I also tried using a temp table (kind of a waste for one int), but when I ran the select on the two tables, I couldn't find a way to reference the temp_tbl simultaneously.

I HAVE to be over-complicating this. Can someone lend me a hand?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-01 : 13:42:10

Update is not going to affect the rowcount in the table, only an INSERT will.

What is the exact requirement? Do you want to know the number of records updated?

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2009-10-01 : 13:43:44

DECLARE @RowCountBefore INT,@RowCountAfter INT

SELECT @RowCountBefore = count(1) from <table_name>
--- your update statement goes here ---
SET @RowCountAfter = @@RowCount

Now the variables @RowCountBefore & @RowCountAfter shound have the data you want.

Let me know if this solves your problem or not.

TechnologyYogi
http://gvphubli.blogspot.com/

SLM09
Starting Member

31 Posts

Posted - 2009-10-01 : 13:55:30

@Vijay - Sorry, bad choice of words on my part. When I said "update", I didn't mean running the command UPDATE, but just updating the table (through the use of an INSERT)

@Gvphubli - That is EXACTLY what I needed! Thank you!!!

SELECT @RowCountBefore = count(1) from <table_name>
I used
SET @RowCountBefore = SELECT count(x) FROM <table_name> :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources