C# .NET - deleting all of the records from a database table at one time
Asked By Elvin
11-Feb-12 06:47 PM
I do not want to have to delete every time one by one when I got a really large table data for each record. Is there a way to delete them all at one time? I am using SQL 2008. Can anyone show me some hint or code? Much thanks.
Pat Hartman replied to Elvin
DELETE YourTable.*
FROM YourTable;
But dropping the table may be faster.
Sandeep Mittal replied to Elvin
Truncate is the best option. Below query would delete all records from all the tables. But remember, there should not be any primary key and foreign relationship otherwise this query might not execute successfully. so remove all relationships before running the query.
BEGIN TRAN
DECLARE @QUERY NVARCHAR(MAX)
SELECT @QUERY = STUFF(
(SELECT ';TRUNCATE TABLE ' + name
FROM sys.tables
FOR XML PATH('')), 1,1,'')
SELECT @QUERY
EXEC sp_executesql @QUERY
ROLLBACK
D Company replied to Elvin
Hello
If i could understand your requirement, than you want to delete table records not its structure .
to delete the records simply call
delete from yourtablename if u have any condition than delete from yourtablename where(ifanycondition)
drop tabble will delete the structure of your table, so i dont think that is required in your case
if you want to delete structure also than use drop table name
Regards
D
Sandeep Mittal replied to Elvin
I would suggest using "truncate". It is much fast as compare to delete.
Reason : Delete command create transactions log for rollback
Somesh Yadav replied to Elvin
Hi,
Use SQL Query
DELETE * FROM table_name
kalpana aparnathi replied to Elvin
hi,
Simple solution with loop for deleting all of the records from database try it .
foreach (DataRow row in dt.Rows)
{
row.Delete();
}
Thanks,
Peter Bromberg replied to Elvin
TRUNCATE TABLE TABLENAME
This is very efficient, and it also re-seeds any identity column value. There is absolutely no need to drop a table just to delete all the rows in it.
TableAdapters, DataTables, Rows, RowChangeEvents, RowChangeEventHandlers and DataRelations. To save writing the same method for every DataRow, I have written a method that takes a parent DataRow and a DataRelation and fills the DataRelation's child DataTable with the children of the parent DataRow, as defined by the foreign key in the DataRelation. This all worked fine until I used it with schemas other tables with the same name in different schemas. I can't find any properties of DataRow or DataTable that have this information, which I guess is because it's a SQL Chris SQL Server Programming Discussions CREATE TABLE (1) ALTER TABLE (1) Nvarchar (1) Trigger (1) Foreign (1) Relation.ChildTable.PrimaryKey (1) Relation.ChildTable.TableName (1) ChildPrimaryKey.ColumnName (1) I have got looks like this: CREATE TRIGGER [UniqueTableName] ON DATABASE FOR CREATE_TABLE, ALTER_TABLE AS DECLARE @tableName nvarchar(max) SET @tableName = EVENTDATA().value('( / EVENT_INSTANCE / ObjectName)[1]', 'nvarchar(max)') DECLARE @tableCount int SELECT @tableCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName IF (@tableCount > 1
following Datatype as StudentID = INT, FirstName = VarChar, LastName = VarChar, Gender = Varchar, GPA = Float, MyImage = Vabinary (MAX) This is my Database type but i add the Datatype in in my Parameter to Update(deletedDataSet.Tables["Student"]); deletedDataSet.Merge(deletedDataSet); Button that will Add the Student Record in Datarow of Dataset is or you can say to Grid is Here i take a mistake but for the error above is due to the Data is not updated correctly in Datarow So Consider and tell me where i take the Mistake for the Datatype private void Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints } DataSet modifiedDataSet = changesDataSet.GetChanges(DataRowState.Modified); if (modifiedDataSet ! = null) { StudentDataAdapter.Update(modifiedDataSet.Tables following Datatype as StudentID = INT, FirstName = VarChar, LastName = VarChar, Gender = Varchar, GPA = Float, MyImage = Vabinary (MAX) Or may be Datarow fail to Delete the record or Update the record i don't know please tell
splitter1); S = SR.ReadLine(); foreach ( string s in words) { lblInfo3.Text + = s.Replace( '"' , ' ' ).Trim(); } / / Rubrieken DataRow rowRub = dtRub.NewRow(); rowRub[ "id" ] = (Convert.ToUInt16(dtRub.Compute( "MAX(id)" , "" ).ToString()) + 1); / *if (0 < Convert.ToDouble(words[5])) {* / rowRub[ "geaardheid" ] = "Opbrengst" ; / *}else{ rowRub["geaardheid Kost"; }* / rowRub[ "beschrijving" ] = " " ; rowRub[ "rNaam" ] = words[9]; dtRub.Rows.Add(rowRub); / / Verrichtingen DataRow rowVerr = dtVerr.NewRow(); rowVerr[ "id" ] = (Convert.ToUInt16(dtRub.Compute( "MAX(id)" , "" ).ToString()) + 1); rowVerr[ "verrichtingsdat" ] = Convert.ToDateTime(words[3]); rowVerr[ "bedrag" ] = 0; rowVerr[ "persoonsId" ] = 5 10]; rowVerr[ "detailOmzet" ] = words[12]; rowVerr[ "geboekt" ] = false ; rowVerr[ "commentaar" ] = " " ; dtVerr.Rows.Add(rowVerr); / / verrichtinglijn DataRow rowVerrL = dtVerrL.NewRow(); rowVerrL[ "idVerrichtinglijn" ] = (Convert.ToUInt16(dtRub.Compute( "MAX(id)" , "" ).ToString()) + 1); rowVerrL[ "bedrag" ] = Convert.ToDouble(words[5]); rowVerrL[ "rekeningNr" ] = words[0]; / / id lookup to an instance of an object. Source Error: Line 89: } Line 90: / / Rubrieken Line 91: DataRow rowRub = dtRub.NewRow(); Line 92: rowRub["id"] = (Convert.ToUInt16(dtRub.Compute("MAX(id)", "").ToString()) + 1); Line 93: / *if (0 < Convert.ToDouble(words[5])) Source File: c: \ Documents
I am not understanding how this happens when the query clearly has no "criteria" : SELECT Max(vwClaimDetail.clm_uniqueID) AS MaxOfclm_uniqueID, Max(vwClaimDetail.Match_Policy) AS MaxOfMatch_Policy, Max(vwClaimDetail.client_num) AS MaxOfclient_num, Max(vwClaimDetail.accid_num) AS MaxOfaccid_num, Max(vwClaimDetail.cov_cd) AS MaxOfcov_cd, Max(vwClaimDetail.claim_seq_num) AS MaxOfclaim_seq_num, Max(vwClaimDetail.trans_seq) AS MaxOftrans_seq Max(vwClaimDetail.cme_trans_dt) AS MaxOfcme_trans_dt, Max(vwClaimDetail.dt_type) AS MaxOfdt_type, Max(vwClaimDetail.ext_claim_id) AS MaxOfext_claim_id, Max