c00000fd c00000fd - 1 month ago 8
SQL Question

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

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.
}
}
}

Answer

This will do the select and delete simultanious:

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