Results 1 to 11 of 11

Thread: Delete all records in a table

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    Delete all records in a table

    Hey people. In a bit over my head here. I've got a SQL server up and running. I can open record sets, step through records, etc. So that part is working. But I can't seem to get the .execute function to work. Specifically to delete all records in a specific table.

    Dim cnn As New ADODB.Connection
    cnn.ConnectionString = ConnectionString$
    cnn.Open

    cnn.Execute "delete * from dbo.CellNumbers" <=== Fails
    cnn.Close

    This also fails: cnn.Execute "delete * from CellNumbers" <=== Fails

    Error message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '*'.

    I'm assuming this has something to do with the version of the database I'm running or server side vs local cursors or some such thing?

    This is my connection string:

    Driver={SQL Server Native Client 11.0};Server=myserver.com,myport;Database=MyDB;User Id=MyUser;Password=MyPw$;Encrypt=yes;TrustServerCertificate=yes

    Any help appreciated.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: Delete all records in a table

    Have you tried "DELETE FROM dbo.CellNumbers"? No Asteriks
    https://docs.microsoft.com/de-de/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver15


    EDIT: If that Table is not a Parent/Master to a Detail-Table, you could even DROP TABLE and then CREATE TABLE
    But i wouldn't know what's faster.
    If this should work you would get an "advantage" for free over deleting all records from the table: the Auto-Increment starts at 0 again (if you use surrogate Primary Keys).
    .... at least i would expect it to start at 0 again. Don't have SQL-Server, never used it.
    Correct me if i'm talking BS here
    Last edited by Zvoni; Apr 15th, 2021 at 08:13 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    Re: Delete all records in a table

    Just solved my own problem. Apparently you do not use the *

    cnn.Execute "Delete * from dbo.CellNumbers"

    Should actually be

    cnn.Execute "Delete from dbo.CellNumbers"

    Which seems like odd syntax but when in SLQ's version of Rome I guess...

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    Re: Delete all records in a table

    Thanks Zvoni. Yes, I just stumbled across this. Thanks very much for your quick reply. Seems you were posting at the same time I was.

    Much appreciated!

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: Delete all records in a table

    Quote Originally Posted by Darkbob View Post
    Just solved my own problem. Apparently you do not use the *

    cnn.Execute "Delete * from dbo.CellNumbers"

    Should actually be

    cnn.Execute "Delete from dbo.CellNumbers"

    Which seems like odd syntax but when in SLQ's version of Rome I guess...
    To me it actually makes perfect sense, to not use the '*' in the DELETE statement (contrary to a SELECT).
    In a SELECT i can choose which columns to show, or all with '*'
    But in a DELETE i will always delete the whole row, so it doesn't make sense to have a "column-specifier" for the DELETE-Syntax
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: Delete all records in a table

    Quote Originally Posted by Darkbob View Post
    Thanks Zvoni. Yes, I just stumbled across this. Thanks very much for your quick reply. Seems you were posting at the same time I was.

    Much appreciated!
    You're welcome.
    Any thoughts on my DROP TABLE/CREATE TABLE Approach?
    Wouldn't mind some performance-results with a few 100K/1M rows
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Delete all records in a table

    Zvoni DON"T do DROP and CREATE for the table the better option would be (if not the Parent table in a FK relationship) to use TRUNCATE TABLE schema.tableName. this will also reset identities to 0 but keep any permission that were granted on it in place.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Delete all records in a table

    Yes truncate table is the better way. It is quite a bit faster than delete and does not destroy the table.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: Delete all records in a table

    Gary, thx.
    As i said: Never had/used SQL-Server, so i'm not familiar with the intricacies/pitfalls of it

    But, as i said: as long as that table is NOT a Parent/Master to a Detail-Table.....

    EDIT: Just looked up the TRUNCATE TABLE Statement for SQL Server
    Yepp, it's actually the exact thing the OP wants
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Delete all records in a table

    Truncate table should work with most DB systems that follow the ansi standard.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: Delete all records in a table

    Quote Originally Posted by GaryMazzone View Post
    Truncate table should work with most DB systems that follow the ansi standard.
    TRUNCATE TABLE works for tables which can be DROP'ed TABLE too which is tough.

    Both *don't* work for any table that has a FK pointing to it, which is like 90% of the tables in a DB. For instance you cannot DROP/TRUNCATE your Products table if there is a FK to Products.ID from InvoiceRows.ProductID column.

    The only way to use TRUNCATE TABLE is to first script all FKs to the table, then drop all FKs to the table, then TRUNCATE the table and finally recreate these FKs. . . which is a *very* tedious job provided that a simple table like Products can have like 20 to 50 FKs from various tables in a DB and recreating the FKs can (and will) actually fail with missing keys at the end of it :-))

    So, TRUNCATE TABLE is best used for temp tables with one caveat -- TRUNCATE/DROP TABLE in a stored procedure will force it to be recompiled on each execution so this could be a problem in some use-cases and generally frowned upon by DBAs.

    cheers,
    </wqw>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width