Remove And Archive Records Into Another Table
Mar 5, 2008
Hi,
I currently have one table that lists all projects and tasks within the organisation. One of the table fields is the task status, open or closed. I would like to be able to have a process by which the tasks that are completed are removed from the table and placed into another (archive) table. The same records then being removed from the original table. which then only contains the incomplete tasks. This process could be run at given times during the day or at the point when the status of a task is changed from open to closed, either way each time the process is run it would need to append the rows removed into the archive table. Anyone any ideas on the best way to do this?.
Thanks
View 3 Replies
ADVERTISEMENT
Feb 4, 2015
I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). How to archive the data and then delete the archive data from both tables.
View 9 Replies
View Related
Jul 13, 2007
I searched the forum, but I was unable to find an answer to this. What I want to do is remove the page breaks (horizontal lines) from the web archive, but I've been unable to find anything on the internet about it.
Does anyone have any advice/suggestions? Thanks in advance.
View 2 Replies
View Related
Mar 26, 2004
I have a table called customers that store information about the particular customer. I would like to have a table called Archive so that when I delete a Customer that have not been active for a specific time then the deleted information will be automatically be inserted into the Archive table. Do I need to create the archive table with the same numbers of columns as exactly as the customer table?
I need some basic idea about how this should be implemented.
Any input will be appreciated.
Thanks in advance
View 4 Replies
View Related
Oct 31, 1999
Hello:
The purchased-application mssql 6.5, sp 4 that I am working on has one large table has 13m illion. It the largest table considering thenextlatgest table is only1.75 million rows.
Thew vnedor has made a change to this largest table in recommending changing a data type -- char to varchar. To make this change easier to do,
I want to "archive" older data not necessary for the current year or current processing to another table.
What is the best way to do this archiving?
Any information you can provide will be greatly appreciated. Thanks.
David Spaisman
View 4 Replies
View Related
May 7, 2007
Hi,
In a project with SQL Servcer 2005 the customer is interested to save (archive) content of a table frequently. They want to have possibility to restore the table content as before. I have suggested to export the table every day to a text file and save the file in Visual source safe. If they need there will be possibility to import the text file later. Now I wonder, is there any other way to do archiving of the table content in SQL Server 2005?
Best regards
View 4 Replies
View Related
Mar 14, 2008
I have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
View 9 Replies
View Related
Aug 7, 2015
I'm getting an operand type clash. Text is incompatible with int when attempting to archive table.
The table has several columns defined as Text.
[URL]
View 7 Replies
View Related
Jun 22, 2015
I have some table that need to be partitioned and archive one of the partitions.
I did this in Oracle several years ago but not in SQL Server.
I'm looking for a basic example on how to do this.
I know the basic steps but the examples that I found on the Web were not quite what I'm looking for.
[url][/
Partition an existing SQL Server Table
url]
View 9 Replies
View Related
Oct 20, 2001
I have SQL Server Standart Edition and have a database, MDF file size is 650 MB, log file size is 1,23 GB
I tried to shrink the log file but i didnt shrink, I wanr to remove all records from the log file
How I remove records from the log file I want to shrink this file to 1 or 2 MB
Please help me
View 1 Replies
View Related
Feb 12, 2008
Hi
I have a data in one table like below.
EDITION PRODUCT INSERTDATE
---------- ------------ ----------------------
CNE TN-Town News 12/19/2007 12:00:00 AM
TN TN-Town News 12/19/2007 12:00:00 AM
What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.
How to do that?. Can anybody help me?
Thanks
Venkat
View 14 Replies
View Related
Nov 18, 2006
Guys,
Is there some Transformation or other method to remove outlying records based on an attribute during a Data Flow task?
I have a list of Organizations complete with a list of Products they have bought. I am going to do some data mining / profiling off of this data but first I need to get rid of the top 25% and bottom 25% quantity records by Product. I've looked at Percentage / Row Sampling but they are too simple.
How would this be done in SSIS?
Thanx!
Sincerely,
J'son
View 1 Replies
View Related
Jun 23, 2005
We imported approximately 2.9 million records from our mainframe server
into our SQL Server but have run into a problem. The data in a
few of the fields contains both leading and trailing spaces. An
example of the data would be like this, using periods to represent
spaces:
What we have:
..1A02938.....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to remove
those spaces? The problem is coming up when trying to perform a
SELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zero
results because of the spaces in the database. The datatype of
the filed is char(20) because we need some flexibility on the size of
the data stored.
Any assistance would be greatly appreciated.
View 5 Replies
View Related
Feb 18, 2014
We configured IDERA SQL Safe for backups and restores.we setup an email for notifications. One day we performed manual backup operation for 150 databases from SQL Safe tool. Unnoticed it backed up to C:Backup folder.
We got alerts with the report of backups on C: drive. Then we moved backup files to respective folders.But, I could not clear the records from the report, its been 25 days, still we are receiving the alerts as below.How can I clear this report or to configure or setup anything to avoid this, in future as well.
Below is the sample records from the report. I need this report to be cleared.
Subject: SQL Safe Validation Report
The following files are recorded in the SQLSafe repository, but no longer exist in their locations:
C:BackupLUXOR_DB_GroupEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_InsightEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_IMEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiteEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_SiEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_ICEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_vdbEPF_Diff_201402050045 (1 of 1).safe
C:BackupLUXOR_DB_M_SEPF_Diff_201402050045 (1 of 1).safe
View 3 Replies
View Related
Apr 16, 2007
Is there a way to check if duplicates exists in the incoming textfiles????
View 5 Replies
View Related
Dec 3, 2010
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
View 9 Replies
View Related
Sep 9, 2004
Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
View 7 Replies
View Related
Jan 6, 2014
I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
Structure:
TABLE [dbo].[forecast_data](
[recID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [int] NULL,
[Project_Name] [nvarchar](255) NULL,
[Stoplight_Status] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[code]....
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
View 6 Replies
View Related
Jul 28, 2015
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
View 4 Replies
View Related
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
Aug 18, 2015
I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).
View 5 Replies
View Related
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
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
Mar 1, 2005
Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be great
View 4 Replies
View Related
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
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
Feb 15, 2008
I need a SSIS Package that compares the two tables and removes the rows in the first table with keys that do not exist in the second table. For example....
I have a table of returns based on returnID. In another table I have returnErrors that are based on returnID as well. I want a package that will uses my returns table as a source and compares that dataset to the dataset of the returnError and remove or spilt the data so that my remaining dataset only has returns that have returnErrors. I can do this in T-SQL, but I am looking for a SSIS solution that uses the conditional split transformation or some other transformation(s) combinations.
-- Ryan
View 7 Replies
View Related
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
Oct 14, 2013
I have a table like below
patientidcode
11 101.3
12 102..3
13 103.
14 104..3.
15 109.3
16 105..3
but need like below to remove . (dot)
patientidcode
11 101.3
12 102.3
13 103
14 104.3
15 109.3
16 105.3
View 3 Replies
View Related
Jan 2, 2001
I have small table that has been corrupted.
I'm getting the following error when running a simple select statement:
"could not open FCB for invalid file ID 0 in database 'data_base_name'.
Table or database may be corrupt. connection broken."
I have already created a new table, but I have been unsuccessful at removing the corrupted table. Does anyone know the steps to go about removing this table? And, if anyone has seen this type of error before, why did it occur?
Is it a bug with microsoft SQL7? Any info would be greatly appreciated.
Thanks in advance
View 3 Replies
View Related
Aug 7, 2013
I have a query like this
SELECT TOP 1 * FROM ITAM_RAMS_STAGING
not getting value...seems like lock associated with that table
how to remove the lock if exists
View 2 Replies
View Related
Jan 10, 2008
I deleted a login name from the server Security - Logins. The login name was added to the database (myDatabase) permissions before it was deleted. I meant right click at myDatabase. Select Properties, and the Permissions tab. What is the command for removing the login name from myDatabase Permissions?
I did it from the Query Command window yesterday using maybe EXEC SP_????????? but I could not find it again on the web. Can someone please help me with this?
Thanks.
DanYeung
View 2 Replies
View Related
Jan 29, 2008
Hi All
I have the dbo.OperatingHour It has many duplicates and I want to remove duplicates permanently
The statement below works but when I open the table there are no changes
Insert into OperatingHour(Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays])
(SELECT DISTINCT Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays] FROM OperatingHour)
View 2 Replies
View Related
