| Author |
Topic |
|
Amber_Deslaurier
Starting Member
40 Posts |
 Posted - 2010-06-24 : 20:37:59
|
| Hi,I have 10 gigs of data that is constantly being appended 24/7 I would like to copy all the data into a another table without interrupting the production, what is the best way to do this... I would like to avoid locking the main table. thanks guysamber |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-06-25 : 01:52:20
|
| With data appending or with each new insert on the database, you will have a table lock in each time. so other transactions will have to wait, respectively if you will do a copy of that table to another table.Due to the logging of the data (depends on your sequel version) this process my double the time needed to do the transaction. if this is one-time operation i would suggest you to use INTO statement (Select * INTO NewTable from OldTable), since it doesn't log the transaction.If this is ongoing process i would suggest you to create a read-only database with this table inside and have it mirrored or replicated in time-period needed for your bussines needs.But since INTO statement will also created other transactions to wait, i think the best way to approach this is to copy the database and you won't take it offline and transactions on original database will go flawless. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-25 : 03:03:14
|
| Is this a one time deal or an ongoing process? What are you going to use the copy for? Is it for reporting puposes? High availability? Is the other table on the same server/in the same database? If it's ongoing there are several ways; replication, log shipping, mirroring, manual copy...but to make sure you're not locking the production table your best bet is to use techniques that utilize log readers and that are asynchronous.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Amber_Deslaurier
Starting Member
40 Posts |
Posted - 2010-06-25 : 06:16:07
|
| Hi guys,yes I will be copying on going and its on the same platform (EDW) I am using teradata sql assistant. Is mirroring the same as creating a view? How do I make a mirror code... like syntax? Its a massive database and it keeps growing and I need all the data in there + whatever keeps flowing in... but its vital that its done with the priority of not locking the table since it would cause issues. You mentioned replication, log shipping, mirroring, copy (1st post) etc whats the difference and where can i read about it... I need your expert advice; which one do i use?... please. Thank you. Amber- |
|
|
|
Amber_Deslaurier
Starting Member
40 Posts |
Posted - 2010-06-25 : 06:31:02
|
| I forgot to mention, its for reporting. Its in the same server (I think) but I will need to copy into a seperate database... thank you. Amber- |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-25 : 12:24:38
|
| Sounds like what you want to implement is some form of Delta procesing. Unfortunatly, this can take many forms, so without more information it's be hard to suggest anything specific. |
|
|
|
Amber_Deslaurier
Starting Member
40 Posts |
Posted - 2010-06-26 : 20:37:15
|
| whats Delta processing? The table has a ton of fields what info do you need? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
COPY DATA FROM ONE TABLE INTO ANOTHER