Move Data To Archive Table. Need Suggestions.
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
ADVERTISEMENT
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
Dec 14, 2007
hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
View 5 Replies
View Related
Apr 2, 2008
I don't know what to do here please help! I have 2 tables here and I want to move selected data in one table to another but im not sure how to do it.
Thanks in advance =)
View 4 Replies
View Related
Sep 12, 2004
Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this:
INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED)
SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED
FROM dbo.TABLENAME
WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888'
GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together
Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
View 1 Replies
View Related
Dec 10, 2013
We need to move duplicate data from this sheet to other table also having issue that sometime verifiedmemberID is null as well as verifiermember name is null and also having the values in BCP authorisationcode as well as FPoveridecode but transactionmode/bcpmode is 'n' and also having condition that transactionmode/bcpmode is 'y' but bcpauthorisationcode is blank.
MemberStatecodeMemberDistrictCodeURNCompanyCodeHeadMemberIDHeadMemberNamePatientID
PatientNamePatientGenderPatientAgeTerminalIDHospitalCodeRegistrationNoBlockingUserDateUnblocking
InvoiceNoDischargeInvoiceNoDischargeDescDischargeUserDateAmoutClaimedTransactionMode/BCPMode
UnspecifiedAuthCodeUnspecifiedAuthDateBCPAuthorizationCodeBCPAuthorizationDateFPOverideCode
[code]....
View 2 Replies
View Related
Jul 23, 2005
I have a production application that I am building some upgrades usinga second (empty) copy the database. A few of the upgrades includedchanging the datatypes of a few fields from varchar to int(all thevalues in this column are already numbers)I am not trying to move the data from the production database into theblank database using DTS and the fields that had their datatypeschanged are getting dropped by dts.Is there anyway to move this data easily through dts or through anothermethod and not have the fields with the datatype changes get dropped?I have about 60 tables so it is not as simple as just copying andpasting the data..
View 2 Replies
View Related
Apr 6, 2008
Hey all,
Another varbinary question.
I am trying to move an image stored in a table varbinary(max) directly from one table to another programmatically.
The rest of the data is just nvarchar(50) so I just use a T-SQL select statement in the code behind and feed all of the date into an SqlDataReader, I do this becuse there is some user interaction when the data is moved, so there may be some new values updated when transfering from one table to another, so once the old and possibly new data is stored in seperate variables then I use a T-SQL insert statement to move all of the data into the other table.
Problem is I am not really sure how to handle the image data(varbinary(max)) to just do a straight up transfer from the one table to another. I get conversion errors when trying to handle the data as a string which makes sense.
Not sure what to put for code examples since I really am stumped with this, but here is what is not working.
Dim imageX As String
SqlDataReader Code - imageX = reader("imageData")
Insert code - myCommand.Parameters.AddWithValue("@imageData", imageX)
Thanks in advance,
View 6 Replies
View Related
Aug 22, 2005
I have an application , user will read information in Excel file and insert that data into my application, I think it spend a lot of time. I want to make a tool which move data from Excel file to a table in My application (MS SQL) automaticly. How to do it, anybody has tool or know how to do, pls help me.thanks.
View 2 Replies
View Related
Jul 29, 2014
I have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from db1 to db2, My table contains
employee_no INT,
date_of_attendance datetime,
present varchar(20),
shift_type VARCHAR(20),
marked_by VARCHAR(50)
View 4 Replies
View Related
Oct 12, 2013
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum
View 1 Replies
View Related
Mar 18, 2008
Hello All,
I do have one large table, say "emp" having 80 columns. now as the requirement changes, i have to partition the "emp" table to 8 tables.
I want all of my existing data ["emp" table data] to be there in my new tables . i don't want to delete the existing data from "emp" table.
Cal any one please help me out to resolve this issue.
Thanks
Prashant Hirani
View 5 Replies
View Related
Aug 5, 2015
There is a table in which the data is coming in a massive rate, what are the ways to move those data from that table to another DB in another server (kind of archiving).
View 5 Replies
View Related
May 8, 2015
I am using a WriteBack Partition to receive data from various inputs and appends any new data that I add to the WB partition.
I am able to read the data immediately in the WB partition through a Fact partition query. This is working at this point as desired.
Eventually I want to move the data from the WB partition into Fact Partition. How can I do this, manually and through automation.
View 5 Replies
View Related
Oct 18, 2014
I would like to move the data / findings into a perm table?The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.
USE [MMAUDIT]
GO
CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS
BEGIN
[code]....
View 4 Replies
View Related
Jan 28, 2008
Hi, I'm using sql 2005,
what i want to do is:
first, i want filter data from table A if IC_NO and passport_Number no is null
then after get the result, i want move the result into table B.
my table as below:Table A
NUMBER IC_NO PASSPORT_Number
======= ====== ===========
1. 123 A123
2.
3. 456 A456
your post really highly appreciated
thanks
View 1 Replies
View Related
Mar 25, 2012
Project assigned to me called ''Archive off old data''.As SQL DBA the best way to archive large table is data Partitioning. But one table does not enough in terms of size to do partition and plus table has 32 other tables,view,sps dependencies.The data from that table needs to archive off every week to different database on different server. My question are
1) what is the best way to archive data ?
2) what are steps I need to follow ? Do I need to remove dependencies first and then take out data?.
View 2 Replies
View Related
Feb 4, 2004
Hello, everyone:
What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle?
Thanks.
ZYT
View 1 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
Mar 14, 2008
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
View 10 Replies
View Related
Jun 5, 2002
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
View 2 Replies
View Related
Jun 5, 2002
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
View 2 Replies
View Related
Jul 20, 2005
I have a table contains huge rows of data. Performance issue raised. I amthinking archive some data so that the table will not be that big. The mostconvience way is move it to another table. The problem is: will this solvemy performance problem? or I need to move it to another database to reducethe database size?Regards,TrueNo
View 2 Replies
View Related
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
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
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
Oct 28, 2014
I have the requirement to implement archiving of some database old data.
The database is not used to store files, just table with text/numeric data, some data modification logs.
Which strategies do you usually use to archive old records from a database?
Do you move old/unused data to another similar database in another server?
The data needs to be accessible for read access, even when archived.
View 3 Replies
View Related
Apr 22, 2015
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And the same thing the next day and everyday. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. Some tables have 200Mil+ rows. So it takes forever.
View 1 Replies
View Related
May 5, 2015
I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data.
Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.
My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.
There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.
View 5 Replies
View Related
Jul 31, 2015
I wrote code to archive Data.I created a table that set the maximum number of records to archive and delete and loop until finished or a set a flag in a table to stop archiving. I was able to limit the number of records to commit.
View 9 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 30, 2006
Hello and thanks for your help in advance.
So what I need to do is store applications info, such as application name, path, server it's installed on, etc., into a table.
I thinking of designing the table like this but not sure if this is a good design:
ApplicationInfo
---------------
ID
Application -- pk
Path
ServerName
There are apps that are installed on all servers and there are some apps installed only on a few servers. I was thinking of making the "Application" field unique so that only 1 instance of the application name exists and then comma delimit the "ServerName" field values.
So with this approach records would look like this:
Field Value
-----------------------------------
ID "1"
Application "Adobe"
Path "C:Program FilesAdobe"
Server "ServerA,ServerB,ServerC"
ID "2"
Application "Microsoft Office"
Path "C:Program FilesMicrosoft Office"
Server "ServerB,ServerC"
What would you recommend doing?
View 3 Replies
View Related
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
