Looking For Simple Purge Of Records From A Table

May 21, 2007

greets all, ive got a table with batches of records. each group of records has a batch id as part of the PK in the form BTCXXXX where XXXX is an auto-incremented number. so lets say i have 100 batches of 20k records per batch in the table. so the distinct batch ids are BTC0200 (oldest batch) through BTC0300 (newest batch).
i only want to keep the 90 most recent batches in the table at any given time.
is it ok to just subtract 90 from the last batch id and do something like:

DECLARE @batch_id char(10)
SET @batch_id = 'BTC' + batch_num-90

DELETE FROM ITEM_BATCH
WHERE BATCH_ID < @batch_id

i want to cover if the table has more than 90 batches and if the table has less then 90 batches. is this a feasible approach?

View 4 Replies


ADVERTISEMENT

Purge Records From Table In A Weekly Schedule

Jul 23, 2002

Hello all,

I hope someone can help me with a big problem... I'm using Citrix Resource Management Services with a SQL 2000 database. Their are 15 citrix servers which are all reporting to the SQL database.

The database is expanding very quickly and is becoming slower and slower.

My question is: I want to schedule a purge of old records on a friday afternoon, like this:

WEEK 1 - MON / FRI
WEEK 2 - MON / FRI (Friday's purge records week1)
WEEK 3 - MON / FRI (Friday's purge records week2)
etc...

Is this possible? if yes how do i do this !??!

Thank you very much for any info!!

Daan

View 1 Replies View Related

Simple Count Function - Show All Records For SSN In A Table

Jul 9, 2013

I want my query to list all SSNS that have more than one record in the table. I have this query:

Code:

SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1

It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.

For example

SSN NAME4 COUNT
123445555 WALTER - 4

I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.

View 6 Replies View Related

WARNING. A Simple Cut And Paste Of 8 Records Can Distroy A SQL Server Table

Jul 20, 2005

Today I need to copy 8 records in a table. I have to use Access 200 becauseof the limitation of Enterprise Manager's inability to cope with field withmore than 900 characters. Selected records, cut, paste. I got an erroormessage about not being able to have a null Key_ID (I copied the reords andtried to paste the Key_ID as part of the records - normally I hide theKey_ID).Now I can't access either the new records or the originals that I was tryingto copy (because, it would seem, they have identical primary keys). I alsocannot export the table via DTS 'unspecified error' and 'integrityviolation'.Or delete the offending records with a Query Anaylyser delete query.Basically the entire SQL Server database has been destroyed with a couple ofkeystrokes.Now, I've being developing database applications for over 20years and theone thing, maybe the only thing I expect from a database server is toprotect the integrity of my data. SQL Server does not, it would seem. Theserecords aren't just any random unimportant records either. They contain the'create views' that my entire application require to function and each oneapproaches the 8000 record limit and have take years to perfect and justchecking that the table is valid could take me days.

View 5 Replies View Related

Purge A Big Table

May 14, 2007

Hi all I have table with about 67 million records
that are marked for deletion.

I know that I can

DELETE from table
WHERE ToBeDeleted='t'

But this may be too big a task for the server considering the amount
of data to delete at once. And if it runs out of resources and errors then nothing gets deleted....

Is there a way to segmant or loop so i can delete like 100k records at a time?

Many thanks in advance

View 8 Replies View Related

Transact SQL :: How To Purge Data From A Table

Nov 4, 2015

We have staging table in which data is dumped from files . The staging table is truncated for every load . In order to retain data from staging table we are creating staging_purge table which hold the staging data. what is the fastest way  to copy data from staging to purge table without impacting the load process.

View 13 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

SQL Server 2012 :: Purge Process On A Large Table

Jan 9, 2014

I am attempting to do a rather simple purge task on a very large table. This task will need to take place daily and delete records older than 6 months out of the database. On first pass this will delete well over 130 million rows. I thought the best way to handle this is create a proc and call the proc from a SQL Agent Job that runs nightly. Here is an example of the script:

CREATE PROCEDURE usp_Purge_WCFLogger
AS
SET NOCOUNT ON
EXEC sp_rename 'dbo.logs', 'logs_work'
GO
SELECT * INTO dbo.Logs_Backup FROM dbo.Logs_Work WHERE TIMESTAMP < DATEADD(month, -6, GETDATE())

[Code] .....

View 3 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data  in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View 4 Replies View Related

Help! A Simple &#39;update Records&#39; Question

Nov 19, 1999

Hi there,

I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.

When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.

Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com

View 1 Replies View Related

Simple Cursor To Combine Records

Sep 28, 2007



I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them.

My data

HDR DMCD
107 TEX
107 AIR
107 LG
108 TEX
108 CAR
109 SM

I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e.

107 TEX AIR LG
108 TEX CAR
109 SM



This is my attempt at the cursor so far


SET NOCOUNT ON

DECLARE @AACODE varchar(50),@hdr varchar(20),@dmcd varchar(20)

DECLARE AAROW_cursor CURSOR FOR

SELECT aaglhdrid,aatrxdimid

FROM aag30003



OPEN AAROW_cursor

FETCH NEXT FROM AAROW_cursor

INTo @hdr, @dmcd

WHILE @@FETCH_STATUS = 0

BEGIN

set @aacode=@aacode+@dmcd

select @hdr,@dim,@aacode



FETCH NEXT FROM aarow_cursor

INTO @hdr, @dmcd

END

CLOSE AAROW_cursor

DEALLOCATE aarow_cursor

View 5 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table

Oct 21, 2015

I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.

SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY

[Code] ...

It's only returning two rows (the students where they have a row in the HomeworkLogLine table). 

View 3 Replies View Related

Problem Of Duplicate Records With A Simple Insert Statement !

Oct 11, 2007

 
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since.
We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.
My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID.
How are these duplicate records inserted with a simple insert statement & update ??
Is this a Locking problem or Transaction problem I have no idea.
Any ideas will be of great help
Thanks in Advance
Stored Procedure Used

CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO 

View 4 Replies View Related

DB Engine :: Can Deleted Records Be Recovered In Simple Recovery Model?

Aug 21, 2015

Can the deleted records be recovered in Simple Recovery model database? Are the delete logs present there?

View 3 Replies View Related

Purge

Aug 29, 2007



Hi Everyone.

i have a big database and for each table i have Active field (N/Y) and i wanna delete all record the active N. how i can do that without delete from each tables and looking to details tables before. our database have a complex relations.
please any solution.

thanks alot.

View 4 Replies View Related

How Do I Select All Records In One Table That Have NO Related Records In Another Table?

Jul 20, 2005

I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks

View 2 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.

In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously

While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.

Is there any SQL Server hints to avoid blocking ..

View 3 Replies View Related

T-SQL (SS2K8) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

Purge Time

Oct 26, 2003

All, I records that have two columns, date col and time col.

I am trying to write a SQL statement to extract the rows that fall within a range of time.
Eg current date/time + 10 hours and
current date/time - 10 hours.

Kindly advise how can I do that.
Thanks.

View 4 Replies View Related

Purge .ldf File

Jul 23, 2004

I'm assuming that the .ldf file houses all the transaction logs, well mine is getting quite large (like 6 times the size of my .mdf) and I'm wondering if there is a way to purge logs back to a specific date. Can anyone give me some direction? Thanks

View 2 Replies View Related

Purge Database

Feb 8, 2005

Is there any way to purge/truncate a database where the dataase is full or does not have free space ?

View 4 Replies View Related

Archive And Purge

Dec 13, 2007

A client of mine just asked about Archiving and Purging their data (based on different time constraints). For isntance, certain data would get archived after 3 years over to a different database, others purged all together. My thoughts off the bat are store procedures run on a job schedule.

Any other thoughts or ideas?

View 3 Replies View Related

Purge A Log File

Aug 9, 2006

Hello,



I would like to use the built in logging feature to log to a text file.



Is there a way to purge the log peridically (only keep enties for the last 30 days, etc).



Thanks,



Michael

View 1 Replies View Related

SQL Server 2005 Purge?

Jun 9, 2006

Greetings,
I have a SQL Server 2005 database which is populated with test data.  I need to copy this database to a new instance and then purge the copied database of it's contents for the next round of testing.
I know there exists a Copy Database Wizard w/in the SQL Server Management Studio; I'm assuming I need to perform this first, then purge the existing data w/in the copy.  The Copy operation looks pretty straight forward, but I haven't a clue on how to perform the purge.
Can someone help?
Regards,
Loopsludge

View 1 Replies View Related

Purge Log Dump Device

Feb 22, 1999

Suppose I am appending to my transaction log dump device every half hour,thus adding 48 log dumps per day. How can I purge my transaction dump device to only keep the last 1 week's worth of these logs. I do not wanted to issue an INIT in the command, because this will wipe it completely. I noticed an EXPIREDATE and RETAINDAYS parameter for the DUMP command. Can I use these to selectively purge the backup device or will these allow the device to be wiped clean also?

Thanks in advance,
Ed Molinari
Emerald Solutions

View 2 Replies View Related

DB Backup File Purge

Apr 25, 2002

Anyone have anything hints on getting the option of DELBKUP to work. I have a maintenance job that backs up the database. Also set to delete backups older than 1 day. This doesn't seem to be working.

Any thoughts.

Thanking you in advance.

View 1 Replies View Related

Why Won't My Transaction Log Backups Purge

Oct 5, 2004

Hi

We are running SQL Server 2000 and making full database backups using a maintenance plan. The transaction logs are being backed up via a separate plan.

However, the transaction log backups aren't purging but the database ones are. They are both run under the same id and both write to the same directory. Old text reports also get purged fine. I've got 'Delete older than 1 week' ticked for everything.

Can anyone think of anything else to check ?
Thanks

View 7 Replies View Related

DB Mirroring Monitor Job - Purge Old Data?

Dec 14, 2006

Hi,

Not sure if this question makes sense, but is it necessary to purge old data in msdb tables used by the db mirroring monitor job?

I'm just wondering if an insert into the data table every minute of the day would still be needed a month from now. I'm thinking this data would be useful for the purpose of "alerts" and to have access to its recent history, but other than that, is it recommended (or necessary)? Would these records keep accumulating until manually purged?

TIA.

View 7 Replies View Related

Automatic Purge Of Transaction Log Data?

Jan 25, 2007

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

View 4 Replies View Related

SQL 2012 :: How To Purge Old Store Procedures From Clients

Jul 11, 2014

I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.

Here's the DMV I am planning to use:

SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count

[Code] ....

I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?

View 5 Replies View Related

Baffled. How To Purge Old Backups With Maint Plan

Apr 13, 2006

I'm missing something basic...

Trying to create a new job from sql server 2005 Management Studio. The nifty wizard under Management provides options to create backups, and clean history, but I cant seem to find out how to create a job to purge old backups.

Looking at the commnand line Sql Server Agent Job shows:

/SQL "Maintenance PlansFull Backups" /SERVER "JFSLIB-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

But as you can see, doesn't help a novice like myself, as it appears to be command options to run the "integration" package.

Any help is appreciated. Thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved