-
Apr 15th, 2021, 07:58 AM
#1
Thread Starter
Fanatic Member
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.
-
Apr 15th, 2021, 08:08 AM
#2
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
-
Apr 15th, 2021, 08:15 AM
#3
Thread Starter
Fanatic Member
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...
-
Apr 15th, 2021, 08:16 AM
#4
Thread Starter
Fanatic Member
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!
-
Apr 15th, 2021, 08:19 AM
#5
Re: Delete all records in a table
Originally Posted by Darkbob
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
-
Apr 15th, 2021, 08:20 AM
#6
Re: Delete all records in a table
Originally Posted by Darkbob
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
-
Apr 15th, 2021, 08:40 AM
#7
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
-
Apr 15th, 2021, 08:54 AM
#8
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.
-
Apr 15th, 2021, 08:56 AM
#9
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
-
Apr 15th, 2021, 09:16 AM
#10
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
-
Apr 15th, 2021, 09:46 AM
#11
Re: Delete all records in a table
Originally Posted by GaryMazzone
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|