verify insert statement - 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

verify insert statement

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-15 : 13:19:42

If I insert 100 records into a table, how can I verify how many succeeded or failed ?

Could I check the insertTime to verify this ?

Thanks


mytable(name varchar(20), insertTime dateTime)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-15 : 13:30:58

Wouldn't you know how many succeeded or failed based upon errors returned?

Does your insertTime have a default constraint of GETDATE?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 13:32:44

If it's a single statement then @@rowcount will show how many rows were inserted (if there are no triggers).

But an insert will either succeed or fail so @@error or a try catch block will show you errors - anything else will be a success.
The inserttime (whatever that is) won't show anything.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-15 : 14:09:28

I want to do this way, it will work ?

Thanks


declare @insertDate datetime, @insertCount int
set @insertDate = getDate()
select @insertDate 

begin transaction

insert into TestDB1..market_def(name, lst_mod_ts) values('eee', @insertDate)

select @insertCount = COUNT(*) from TestDB1..market_def where lst_mod_ts = @insertDate

IF  @insertCount<>1
BEGIN
    print 'roll back transaction';
    rollback transaction
END
ELSE
BEGIN
    print 'success';
    commit transaction
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-15 : 14:26:06

There is no point to run a COUNT(*) after running an insert statement that inserts one row. Instead, use @@ERROR to know if it succeeded or not.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 04:19:38

>> I want to do this way, it will work ?

No - unless lst_mod_ts has a unique index and there are no triggers or suchlike to update it.
Several things are bad about this.
There is no need for a transaction around a single statement - the transaction is implicit.
It makes two accesses to the table when one is needed.
The transaction is held for longer than necessary - leading to blocking and deadlocks.
It shows a lack of understanding of how databases work.
I could go on.

Have a look at try catch blocks.
A single statement will either succeed - in which case there is no need to check, or fail - in which case there is no need to check. It is not possible for an insert to fail without and error.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-16 : 08:58:01

sorry , I made confusion here. The code above is just for testing. Actually I have 264 insertions.


It makes two accesses to the table when one is needed.
The transaction is held for longer than necessary - leading to blocking and deadlocks.

I don't understand this . what is "two accesses " ? why deadlocks ?

I will try @@ERROR
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources