Can I first SELECT and then DELETE records in one t-SQL transaction?

Question

I can't figure out if this is an acceptable operation. I need to select records from the SQL Server 2008 database and then delete them, all as a single transaction from an ASP.NET code. Note that the .NET code must be able to retrieve the data that was first selected.

Something as such:

SELECT * FROM [tbl] WHERE [id] > 6;
DELETE FROM [tbl] WHERE [id] > 6

I'm trying it with the SQL Fiddle but then if I do:

SELECT * FROM [tbl]

I get the full table as if nothing was deleted.

EDIT As requested below here's the full .NET code to retrieve the records:

string strSQLStatement = "SELECT * FROM [tbl] WHERE [id] > 6;" +
    "DELETE FROM [tbl] WHERE [id] > 6";

using (SqlCommand cmd = new SqlCommand(strSQLStatement, connectionString))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
            //Read values
            val0 = rdr.GetInt32(0);
            val3 = rdr.GetInt32(3);
            //etc.
        }
    }
}

@PreetSangha: You mean .NET code? Or SQL? If you mean the latter, it's kind like what I showed above. — c00000fd, Jun 23 '13 at 04:53
Why do you want it done in the same transaction? There's no need to roll back a SELECT. Is it that your application needs to know the set of records that are about to be deleted? — mbeckish, Jun 23 '13 at 04:55
Your sqlfiddle works just fine. See http://sqlfiddle.com/#!3/08da7/11 — peterm, Jun 23 '13 at 04:56
@peterm: Oh. Maybe I just don't know how the SQLFiddle works :) Does it reset the table after I run my query? — c00000fd, Jun 23 '13 at 05:00
The code **will be** something such as...? Does that mean you haven't actually tried it yet to know if it works or not? Premature optimization is trying to improve code you don't know needs improvement; is this a case of premature problem solving ("Can you solve the problem I don't have yet")? — Ken White, Jun 23 '13 at 05:01
@KenWhite: I was trying it out in Fiddle to see if it'd work before attempting to code it but then got the unexpected results from the Fiddle, and thus I'm asking here... — c00000fd, Jun 23 '13 at 05:04
@c00000fd sqlfiddle resets the table schema only if you hit `Build Schema` or open a fiddle from a link (which automatically builds schema for you). — peterm, Jun 23 '13 at 05:04
SQLFiddle is for MySQL. Your tags say SQL Server and T-SQL. :-) They're not the same. Test your queries in SQL Server Management Studio; you can create a temporary table to do so (`CREATE TABLE #test (...)`). — Ken White, Jun 23 '13 at 05:07
@peterm: So why does it show the full table if I run my query and then erase it and do `SELECT * FROM [tbl]`? — c00000fd, Jun 23 '13 at 05:07
@KenWhite: No, I set it for SQL Server 2008. It's right on top. I also updated my C# code above to show what I mean to do. — c00000fd, Jun 23 '13 at 05:08
Testing in SSMS would show you what the problem is here. The return value from multiple SQL statements is the result of the last statement run, which in this case is the `DELETE`. There are no rows returned from a `DELETE`, so there is nothing to read for `val0` and `val3`. — Ken White, Jun 23 '13 at 05:12
@KenWhite: OK. That's what I was afraid. Thanks. So how would you do this? As two execute statements? Or what user2246674 suggests below? — c00000fd, Jun 23 '13 at 05:17
I posted a suggestion. I'm not sure which would work best for you, to be honest. You can probably test pretty easily to find out. :-) — Ken White, Jun 23 '13 at 05:18

score 26 · Accepted Answer · answered Jun 23 '13 at 05:51

26

This will do the select and delete simultanious:

delete from [tbl] output deleted.* WHERE [id] > 6

answered Jun 23 '13 at 05:51

t-clausen.dk

43,517
12
59
92

Ooh, very nice and simple! I believe this approach avoids having to deal with explicit transactions entirely. – user2246674 Jun 23 '13 at 06:37
Hi, How do I get data that deleted ? – mdtuyen Dec 22 '16 at 03:01
1

To capture output into a table variable, use the following: delete from [tbl] output delete.* into @tableVar where [id] > 6 – Michael Erickson Sep 28 '17 at 18:07

score 1 · Answer 2 · edited May 23 '17 at 12:34

1

It is possible to select and delete in the same transaction as long as both operations are enlisted in the same transaction.

Look at this post Transactions in .net

edited May 23 '17 at 12:34

Community

1
1

answered Jun 23 '13 at 04:52

TGH

38,769
12
102
135

Do you mean that I have to put `BEGIN TRANSACTION` and then `COMMIT` at the end? – c00000fd Jun 23 '13 at 04:54
Yes, but I would recommend that you manage the transactions through .Net and not the DB. That gives you better control from code – TGH Jun 23 '13 at 04:55
Can you elaborate on this "manage the transactions through .Net" please? – c00000fd Jun 23 '13 at 05:00
Check out the link I added – TGH Jun 23 '13 at 05:01

user2246674 · Answer 3 · 2013-06-23T05:26:39.800

1

The "easiest" way to achieve transactions with a compatible provider (SQL Server works great!) is to use a TransactionScope. Just make sure the scope is created before the connection is opened so that everything is correctly enlisted.

The content of the SelectStuff and DeleteStuff methods doesn't matter much - just use the same connection, don't manually mess with the connection or with transactions, and perform the SQL operations however is best.

// Notes
// - Create scope OUTSIDE/BEFORE connection for automatic enlisting
// - Create only ONE connection inside to avoid DTC and "advanced behavior"
using (var ts = new TransactionScope())
using (var conn = CreateConnection()) {
    // Make sure stuff selected is MATERIALIZED:
    // If a LAZY type (Enumerable/Queryable) is returned and used later it
    // may cause access to the connection outside of when it is valid!
    // Use "ToList" as required to force materialization of such sequences.
    var selectedStuff = SelectStuff(conn);

    DeleteStuff(conn);

    // Commit
    ts.Complete();

    // Know stuff is deleted here, and access selected stuff.
    return selectedStuff; 
}

edited Jun 23 '13 at 05:26

answered Jun 23 '13 at 05:07

user2246674

7,621
25
28

Thanks. It's an interesting approach. I didn't know that that class existed. I need to try it. – c00000fd Jun 23 '13 at 05:12
Also make sure you get the isolation of the transaction right. – Preet Sangha Jun 23 '13 at 06:03
@PreetSangha Good call - I normally default (as does TransactionScope) to serializable for update operations. Yeah, it's lazy and the correct isolation level is a whole different topic, but it'll either "work right" or "won't work". – user2246674 Jun 23 '13 at 06:35
Also make sure SelectStuff() is not deferred. It has to completely return the data before the scope completes. – Preet Sangha Jun 23 '13 at 07:11

score 0 · Answer 4 · answered Jun 23 '13 at 05:18

0

The return value from multiple SQL statements is the result of the last statement run, which in this case is the DELETE. There are no rows returned from a DELETE, so there is nothing to read for val0 and val3.

There are two solutions I can think of here:

Change your code to expressly start a transaction, perform the SELECT, read the values, and then issue the DELETE, or
SELECT into a #temp table, execute the DELETE, and then SELECT from the #temp table, do what you need to with the rows, and then DROP th.

answered Jun 23 '13 at 05:18

Ken White

123,280
14
225
444

Thanks. The reason I was doing it as I showed it above is because I need this operation to be executed `atomically`. – c00000fd Jun 23 '13 at 05:30
There's no way to do it atomically, AFAIK, because the result of scripted statements is the result of the last statement executed. The second solution is probably the closest you're going to get, because it will remove the rows quickly (preventing other connections from reading them) and preserve them so you can still access them. – Ken White Jun 23 '13 at 05:36

Can I first SELECT and then DELETE records in one t-SQL transaction?

4 Answers4