DELETE*FROM table

Question

Is there a way (similar to the below code) to delete all the rows in a specified table using c#?

SqlConnection con = new SqlConnection(conString);
con.Open();

string sql = @"DELETE*FROM compsTickers;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

Right now i'm getting an error:

Incorrect syntax near '*'

Solution 1

There's nothing wrong with your C# code; that's an SQL syntax error.

Anyway, there's no need for the *. You delete rows, not columns, from a table, so you don't specify columns to delete:

DELETE FROM compsTickers

Solution 2

You don't specify columns DELETE FROM compsTickers is enough.

(Also TRUNCATE TABLE compsTickers will do, it's a different way of deletion, that could make sense depending on your transaction log settings)

Solution 3

Just remove the * as it isn't needed to for DELETE statements

string sql = @"DELETE FROM compsTickers;";

Solution 4

DELETE (Transact-SQL) (link)

FROM is optional. You're always DELETing from a table, so all you needed was (you don't even need a statement delimiter, for a single statement):

DELETE compsTickers

Which empties the table

• but does not reset the identity column, if any

View more solutions

Comments

[locoboy]

is there a reason there's no need for a *?

[Mark Redman]

Truncate will also reset IDENTITY fields, which may or may not be desired.

[BoltClock]

As explained by Mark Redman under cairnz's answer, if you're looking to reset your identities while emptying your table, you should use TRUNCATE TABLE instead.

[cairnz]

@cfarm54 - you delete a complete row - there's never a "partial" delete. You can UPDATE however to blank out certain columns, or set to NULL or whatever you wish.

[BoltClock]

Is the optional FROM clause a T-SQL thing or part of standard ANSI SQL?

[RichardTheKiwi]

It's SQL Server only (I linked to Books Online instead of SQL92 reference) but my crystal ball tells me that is what the OP is using.

[BoltClock]

Looks like it. I'm just curious, is all :) +1

[locoboy]

This is in a clear database method so maybe it's ok in this case?

[Hardwareguy]

+1 for truncate, which is usually faster and resets the identity fields.

[qujck]

con.close() will not compile and why are you trying to close a new connection? i will be zero if the table is empty so i == 0 is a success.

[Aishwar C Nigam]

i always pass the connection string globally, here its just to show that connection string exists.

[Quality Catalyst]

There should be no * at all.