delete all records in SqlServer table
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)
........
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)
........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
never heard of that?
however, TRUNCATE might not be possible, ie when you have foreign keys pointing to your table, for example.
Looks like I need more coffee :)
ASKER
ok i testd...
But the aline:
rsSql.Open TableName, CnSql, adOpenKeyset, adLockOptimistic
is need in this contest?
But the aline:
rsSql.Open TableName, CnSql, adOpenKeyset, adLockOptimistic
is need in this contest?
ASKER
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)
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)
I think that this question is not relevant to your initial question and that you have to put an other post.
But
But
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)
>>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.