?
Solved

delete all records in SqlServer table

Posted on 2008-11-06
10
Medium Priority
?
624 Views
Last Modified: 2012-05-05
1) how to delete all record in sql table?
2)  and how to assign a var in sql instead to use a fixed name of table (in effect the var is dinamilcly fille from other part fo code)
 
This is my conn and code in a VB 6.0:

Sub ADO_ACCESS_To_SQLServer()

'DA ACCESS A SQL

Set rsSql = New ADODB.Recordset
Set CnSql = New ADODB.Connection

ServerName = "CL151F4500\SQLEXPRESS"
DatabaseName = "MASTER"
TableName = "SOSPESI"
UserID = ""
Password = ""

CnSql.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";"

rsSql.Open TableName, CnSql, adOpenKeyset, adLockOptimistic

SQL = "SELECT * FROM SOSPESI"
CnSql.Execute (SQL)
SQL = "truncate table SOSPESI"
CnSql.Execute (SQL)
........
0
Comment
Question by:sal21
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 12

Accepted Solution

by:
Dimitris earned 1000 total points
ID: 22895133
Try this
SQL = "SELECT * FROM " & TableName
CnSql.Execute (SQL)
SQL = "truncate table " & TableName -- Try not to use truncate because sometimes locks the entire database Use delete statement instead "DELETE FROM " & TableName
CnSql.Execute (SQL)

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22895135
you mean:
Set CnSql = New ADODB.Connection 
ServerName = "CL151F4500\SQLEXPRESS"
DatabaseName = "MASTER"
TableName = "SOSPESI"
UserID = ""
Password = "" 
CnSql.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";" 
SQL = "truncate table [" & TableName & "]"
CnSql.Execute SQL

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22895144
sal21 said:
>>1) how to delete all record in sql table?

To delete:

DELETE FROM [TableName]

To truncate:

TRUNCATE TABLE [TableName]

Truncating is much, much faster, but the deletions are not logged, so you cannot roll back the transaction.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22895145
>Try not to use truncate because sometimes locks the entire database
never heard of that?

however, TRUNCATE might not be possible, ie when you have foreign keys pointing to your table, for example.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22895150
Looks like I need more coffee :)
0
 

Author Comment

by:sal21
ID: 22895206
ok i testd...

But the aline:

rsSql.Open TableName, CnSql, adOpenKeyset, adLockOptimistic

is need in this contest?
0
 

Author Comment

by:sal21
ID: 22895244
ahhhh....
and how to order by desc all record based 2 filed, is correct the line:

    SQL = Empty
    SQL = "SELECT PROVA12, PROVA04 FROM SOSPESI ORDER BY PROVA12 DESC"
    DB2.Execute (SQL)
0
 
LVL 12

Expert Comment

by:Dimitris
ID: 22895339
I think that this question is not relevant to your initial question and that you have to put an other post.

But
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22895567
the SELECT is not relevant, you don't need to query the data you want to delete (especially as you want to delete all the rows)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question