February 24, 2004 at 7:26 am
I wish to backup the contents of a table to a seperate table, clear the original table and then import new data. Can somebody please advise the best way of doing this ? I was thinking of using EXEC sp_rename 'tblInventoryCT', 'tblInventoryCTBackup' ! This process will run daily effectively shuffling the data each day into a backup table. Cheers Mike
February 24, 2004 at 5:51 pm
Rename will not work after the first day. It can not be renamed to an existing table.
Method of backup table depends on amount of data and tracking design.
insert, bcp out/in or temp table approach are some options.
February 24, 2004 at 6:19 pm
What happens with the backup. Are you wanting to keep all your data or just prior days?
If that then consider creating a backup table and do something like so.
BEGIN TRANS
TRUNCATE BACKUP TABLE
DROP ANY INDEXES ON BACKUP
COPY DATA FROM MAIN TO BACKUP
TRUNCATE MAIN
DROP ANY INDEXES ON MAIN
POPULATE MAIN
READD INDEXES MAIN AND BACKUP (This was done to save insert time)
CHECK FOR ERROR
IF ERROR ROLLBACK TRANS
IF NOT COMMIT TRANS
As long as the whole process is in a transaction even the truncate will rollback. Test for yourself the above is concept, if someone has time they can probably post a scripted version.
February 25, 2004 at 2:41 am
Many thanks for the replies, basically the tables hold 122K inventory items exported from a VAX system. The only reason why I make a backup is so I can compare the two tables the next day to look for price changes or any new items that have been added over night. What I tried was to creat a DTS package
February 25, 2004 at 2:51 am
SORRY MESSAGE AGAIN ! Many thanks for the replies, basically the tables holds 122K inventory items exported from a VAX system. The only reason why I make a backup is so I can compare the two tables the next day to look for price changes or any new items that have been added over night. What I tried was to create a DTS package 1. Execute SQL Task: Delete BACKUP table 2. Transform Data Task: Copy MAIN into BACKUP 3. Execute SQL Task: Delete MAIN table 4. Transform Data Task: Copy VAX DATA into MAIN 5. Execute SQL Task: DBCC SHRINKDATABASE('MAIN') GO BACKUP TRAN MAIN WITH TRUNCATE_ONLY GO 6. Execute SQL Task: If none of these above options fail then put a 1 in the status field of a different table otherwise put a 0 in the status field. The user can see the status of this within their browser window. Not sure if this is the best way to achieve this ! Also not sure how the BEGIN TRAN & ROLLBACK can be integrated into this not that it is that much of a worry ! You mentioned the TRUNCATE command is this basically the same as what I'm doing at the last stage ? Cheers. Mike
February 25, 2004 at 8:58 am
Can you just delete the data and do an insert statement from the current table to the backup.
Trancate Table Backup
Insert Into Backup
Select * from Original
Truncate Table Original
bulk insert Original from File
You could create a stored procudrue form this or an job and have it run on a timer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply