Table copy

Question

Table copy

paul.s.vidal

SSC Eights!

Points: 805
More actions
December 16, 2016 at 7:13 am

#332795

hI all,
how can i copy a table with an specific amount a data.
example:
myTable has 5 millions rows.
I will create myNewTable as a copy of myTable but with only 1 million data.
myNewTable will replace myTable using table rename and myTable will be saved as history data for research.
The table needs to be available all the time.
How can I achieve this with minimum downtime ?
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

How active is your table going to be? Is it going to be in constant use? Are rows likely to be added to your existing dataset while you're trying to do this?

If so, would it not be a better idea be to insert the entire dataset into another table, and then delete the rows that are no longer applicable from your source table?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

paul.s.vidal SSC Eights! Points: 805 More actions · Answer 2

It has to be always active. we are doing the deletion right now and is taking forever which affect performance, logs,mirroring etc.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

paul.s.vidal (12/16/2016)
It has to be always active. we are doing the deletion right now and is taking forever which affect performance, logs,mirroring etc.

Is the DELETE able to make use of an index? You might also want to perform it in batches, of say, anywhere from 100 rows to perhaps 10,000 rows, depending on just how much of delete can happen without impacting performance. You could set up a loop that keeps going in batches of N deletes and waits some number of seconds or minutes between each batch.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 4

paul.s.vidal (12/16/2016)
hI all,
how can i copy a table with an specific amount a data.
example:
myTable has 5 millions rows.
I will create myNewTable as a copy of myTable but with only 1 million data.
myNewTable will replace myTable using table rename and myTable will be saved as history data for research.
The table needs to be available all the time.
How can I achieve this with minimum downtime ?
Thanks in advance.

Could you post the CREATE TABLE statement and the Constraints/Indexes for this table? I think I might be able to help a bit after work tonight or maybe tomorrow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Paul, I think you've outlined the steps pretty well. Just don't forget to rename the original 5M row table with its new name before renaming the new table to the original table name. Like Jeff asked for, the next step is to look at the DDL for base the table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

kevaburg SSCoach Points: 18131 More actions · Answer 6

I had a similar Problem and used an Archive table for records that aren't currently active.

On a regular Basis you can copy the rows from MyTable into ArchiveTable and then delete the appropriate rows from MyTable.

You need to remember to use small batches in the copy and delete actions to prevent unnecessary preformance loss.

As a side note, don't forget a cleanup process on the Archive table to prevent it bloating beyond usefullness.

paul.s.vidal SSC Eights! Points: 805 More actions · Answer 7

This is what exactly we are doing with an store procedure. Deleting in batches of 1500 rows looping 5 times and then when traffic is slower we do it again and so on.

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 8

Why cant you create a new table and insert into that all records and delete the unwanted data from original table ? This will make sure the table is available all the time. Is there any trigger or FK relationships limiting data deletion from parent table?

kevaburg SSCoach Points: 18131 More actions · Answer 9

Rechana Rajan (12/20/2016)
Why cant you create a new table and insert into that all records and delete the unwanted data from original table ? This will make sure the table is available all the time. Is there any trigger or FK relationships limiting data deletion from parent table?

This is a solution that has been suggested here several times......