One of my customer is asking to delete 95% of the rows in an table which has around 645708 rows in a table.Now my concerns are that , what criteria we need to follow while deleting huge records in table, what are the steps we need to be taken care of?And after deletion, what are the maintenance tasks we need to perform to be up to mark without any issues.Lastly will deletion of 95 % of rows improve performance of a table?
View 7 RepliesI have a table...say tb1 of 20 columns which has 2.7 million rows. There is no PK and the only way of identifying a unique row can be done with combination of column1+column2+column3.
Can anyone help me how to idetify the duplicate rows and also delete the duplicate rows. And to commit after every 5000 rows.
ITS VERY URGENT....Thanks in advance.
Hello,
I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.
I declared a dynamic cursor but it does not work.
Does anyone know how I can do this??
Thanks :)
i am using this statement for deleting a single row in sql table.
"DELETE FROM Random WHERE NewID= '" & strwinner & "'"
where "strwinner" is the variable which contains the row to be deleted.
the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something.
iam executing this statement by using ExecuteNonQuery in my .aspx page.
please help
hi
i am using sql server 2005 express edition , with asp.net
i am trying to delete a table programmatically
a button on a form , if the client clicked it , then a table
should be dropped .
but always i get an error message , that says "cannot drop table <table name> , becaust it does not exist or you do not have premissions to do that"
could any body help plz
thax
ghassan
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
I have a dts which creates a table which is utilized on my localintranet. The DTS runs without error and the table iscreated/populated/transfered to the appropriate db. Then it appearsthat there is an action on this table which truncates it. I have beenunable to determine the culprit. Can I create a trigger that willcapture truncation? I have tried to create a trigger to capture thisinformation but none that I attempt seem to work on capturing atruncation or a drop table and re-create.Any help would be greatly appreciated.MT.
View 2 Replies View RelatedHello,
I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager.
There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database.
Any ideas?
Thank you for your help!
cdun2
I have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"
View 14 Replies View RelatedDear Friends,
Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way.
Thanks all!
kiruthika
http://www.ictned.eu
Hi all,
I have a table in xyz database and there is no column in table like creation_date or modified_date.
The problem is I want to delete records which has been added in the table before 1st jan 2007.
The size of table is 85 GB
Immediate help would be appriciable.
Regards,
Frozen
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
Thank you in advance.
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.
TIA,
Stella Liu
Hello there,
We are currently setting up out production server to the following requirements:
1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.
The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.
Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.
Thanks for your time,
Ulrik Rasmussen
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
View 1 Replies View RelatedIs there an option to find out the deleted DBs on a server?
------------------------
I think, therefore I am - Rene Descartes
table1 has a PK_tbl1 which matches FK_tbl1 in table2 and the relationship is one to one. Like this:
table1 has columns of UnitId, Code ...
table2 has columns of ItemID, UnitId ...
So if I need remove an item from table1 I must remove the matched one in table2 first.
delete from table2 where UnitId = xxx
delete from table1 where UnitId = xxx
Now I need create a trigger on table1 for deletion:
CREATE Trigger [dbo].[Table1_Delete]
ON [dbo].[Table1]
FOR DELETE
As
[code]....
Hi
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Any help/direction here would be appreciated.
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
View 2 Replies View RelatedOk, so I have an issue, was wondering if anybody else has any suggestions.
I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.
1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2. Table contains about 70 million records
3. Table has 6 indexes associated to it
4. Table has 2 views associated to it.
5. Table has 8 foreign keys associated to it.
I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.
When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.
The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.
Here's a DBCC SHOWCONTIG on our table
DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)
Any suggestions on how to delete records efficiently?
Hello,
I want to delete a 0KB file on one of the disks in my server. Do any of you have any idea how to do it??
Thanks,
Narayan
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.
View 1 Replies View RelatedObviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.
View 3 Replies View Relatedhi,
i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).
can you help?
hi
Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.
------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)
------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.
------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;
// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;
public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}
private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);
// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();
connection.ConnectionString = connectionString;
connection.Open();
command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}
public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";
string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";
executeSql(sql);
}
public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
string sql = "DROP DATABASE " + databaseName;
executeSql(sql);
}
// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";
executeSql(sql);
}
// Insert data
public void insertData(string tableName)
{
string sql;
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);
sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}
public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";
RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}
Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?
Any idea?
Any help will be highly appreciated.
CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@var_DB_contract INTEGER,
@var_CQE INTEGER,
@var_PC INTEGER,
@var_item VARCHAR(7),
@var_AMT_PAID INTEGER,
@var_AMT_RET INTEGER,
@var_ITEM_NEW VARCHAR(1),
@var_quant DECIMAL,
@var_fiyr INTEGER,
@var_amt_result INTEGER,
@var_amt_ret_result INTEGER,
@var_amt_old INTEGER,
@var_amt_ret_old INTEGER,
@var_quant_result INTEGER,
@var_quant_new INTEGER,
@var_quant_old INTEGER,
@Item_new VARCHAR(7),
@var_chk varchar(1)
--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @var_db_contract IS NOT NULL
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk ="Y"
END
ELSE
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk="N"
END
SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)
SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)
SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0)
SELECT @item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @var_db_contract
AND PC_CODE = @var_PC
AND ITEM_NO = @var_ITEM
UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result
where db_contract = @var_db_contract
IF @item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and fy = @var_fiyr
UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result,
qtd = isnull(qtd,0) + @var_quant_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and item_no = @var_item
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"
select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180
If it meets this criteria I can change the select to a delete? Please Let me know what you think
Does anyone has a script/exe for periodic file deletion, using date comparison?
------------------------
I think, therefore I am - Rene Descartes