I'm executing two SQL statements in one step "Delete" then "insert", like this:
BEGIN TRAN t1 declare @id int
delete from table
select @id=isnull(max(trans_id),0)+1 from table
INSERT INTO table field1,field2 values(@id, value1);
IF @@error <> 0
ROLLBACK TRAN t1
ELSE
COMMIT TRAN t1
What happens when I execute this code is that the insert process get executed first before Deleting, and the Id variable gets wrong value not in the same order of the table id.
I don't want to use stored prodcedure.
deletefrom table -- this will delete all rows from table, since there is no WHERE clause. select@id=isnull(max(trans_id),0)+1from table -- this will always return 1, since there are no rows in the table.
INSERT INTO table field1,field2 values(@id, value1); --this
should probably cause an error, since "value1" isn't a valid number.
I'm executing two SQL statements in one step "Delete" then "insert", like this:
BEGIN TRAN t1 declare @id int
delete from table
select @id=isnull(max(trans_id),0)+1 from table
INSERT INTO table field1,field2 values(@id, value1);
IF @@error <> 0
ROLLBACK TRAN t1
ELSE
COMMIT TRAN t1
What happens when I execute this code is that the insert process get executed first before Deleting, and the Id variable gets wrong value not in the same order of the table id.
I don't want to use stored prodcedure.
Thanks
I suggest you adjust table definition by changing id to be an identity column and when inserting, no need to include
id column and let database increment it.
there is where condition I just made an example here
delete from table where id=1
I'm not using Identity type because I want the field in a serial row
1
2
3
not
1
2
4
what happens is that the insert process get executed before deleting
Try putting semi-colons after every statement, and not just the last one. This is especially required if you are running this query from an application, like ASP.NET.
elshorbagy
Member
47 Points
111 Posts
Execute Delete before insert in one statement
Jan 21, 2013 03:47 PM|LINK
Hello,
I'm executing two SQL statements in one step "Delete" then "insert", like this:
What happens when I execute this code is that the insert process get executed first before Deleting, and the Id variable gets wrong value not in the same order of the table id.
I don't want to use stored prodcedure.
Thanks
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Execute Delete before insert in one statement
Jan 21, 2013 04:04 PM|LINK
Is that literally the code you are executing?
delete from table -- this will delete all rows from table, since there is no WHERE clause.
select @id=isnull(max(trans_id),0)+1 from table -- this will always return 1, since there are no rows in the table.
INSERT INTO table field1,field2 values(@id, value1); --this should probably cause an error, since "value1" isn't a valid number.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Execute Delete before insert in one statement
Jan 21, 2013 04:35 PM|LINK
What are you looking at that makes you think the insert happens before the delete?
Also, why are you trying to generate id numbers when the database can do it better?
wmec
Contributor
6228 Points
3226 Posts
Re: Execute Delete before insert in one statement
Jan 22, 2013 02:33 AM|LINK
I suggest you adjust table definition by changing id to be an identity column and when inserting, no need to include id column and let database increment it.
HuaMin Chen
elshorbagy
Member
47 Points
111 Posts
Re: Execute Delete before insert in one statement
Jan 22, 2013 10:45 AM|LINK
there is where condition I just made an example here
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Execute Delete before insert in one statement
Jan 22, 2013 01:33 PM|LINK
Try putting semi-colons after every statement, and not just the last one. This is especially required if you are running this query from an application, like ASP.NET.