| Author |
Topic |
|
satish.gorijala
Posting Yak Master
182 Posts |
 Posted - 2009-07-21 : 02:58:09
|
| Hi, i have a large table with data in state wide. Now i want to split the large table in to small pieces(state wide). Instead of copying data from one table to anotehr table based on condtion, i need to move datato another table? how can i do thisMy Query is Select * INTO State1 from Tbl_Nation where Statecode = 'Ca'Here i am able to get copy of data to table 'State1'. Again i need to delte this state data from Tbl_nation data. Instead of doing this, is there any way to move data?G. Satish |
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2009-07-21 : 03:23:38
|
| I guess no, you need to delete the data manually, once you have copied to new table.What's the need to create seperate table for each state?Mangal Pardeshihttp://mangalpardeshi.blogspot.com |
 |
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-21 : 03:41:24
|
Because my table contians around 5 crore records. I need to update one column in this table by making innerjoin with another table(around 80 lakh records). So i am not able to do this bcz of having large data. That's why i need to split the table in to states and then update the data. Finally i will merge the data.quote:
Originally posted by Mangal Pardeshi
I guess no, you need to delete the data manually, once you have copied to new table.What's the need to create seperate table for each state?Mangal Pardeshihttp://mangalpardeshi.blogspot.com
G. Satish |
|
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2009-07-21 : 04:18:31
|
| Hi,I think you are going in wrong direction.See what you will be doing -Moving 5 crore(50 million for users out side India) to small tables -lots of create and inserts.- Then you will update.- Again move back to main table.Instead of performing these equaly(infact more) time and resource consuming tasks, you should look for some optimizing methods.Have you created any indexes on table?Statecode seems to be an ideal candidate for having an index.Look for data partitioning in books online.Mangal Pardeshihttp://mangalpardeshi.blogspot.com |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-21 : 04:34:24
|
| I would say look into partitioning the table, this is a perfect case for doing this as you have a set amount of States. This will speed up your queries without the changing the actual size of the table. You also don't have to worry about the amount of partitions as they are set.http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-21 : 04:58:40
|
Thanks for your reply. But i think this partition concept is on DBA side. Need more efforts and knoledge on this. It is out of scope for me as i am being a .net developer. Any other methods or suggestions.?quote:
Originally posted by Mangal Pardeshi
Hi,I think you are going in wrong direction.See what you will be doing -Moving 5 crore(50 million for users out side India) to small tables -lots of create and inserts.- Then you will update.- Again move back to main table.Instead of performing these equaly(infact more) time and resource consuming tasks, you should look for some optimizing methods.Have you created any indexes on table?Statecode seems to be an ideal candidate for having an index.Look for data partitioning in books online.Mangal Pardeshihttp://mangalpardeshi.blogspot.com
G. Satish |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-21 : 05:01:37
|
| Read the link, don't just dismiss something that will fix your problem out of hand because you are just a .Net developer.You have come on a SQL Server site to ask a SQL Server question. |
|
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2009-07-21 : 05:10:42
|
| I agree with what Rick has said. Or may be start with an Indexes. You defined any indexes on table? My suggestion create one on statecode column and see what improvements you get on your updates.Mangal Pardeshihttp://mangalpardeshi.blogspot.com |
|
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-21 : 05:16:24
|
Hi Rick, i have gone through the link which u have given and gave my previous reply. Any ways friends u suggested the good things.. why i mention out of scope is, not having much knowledge and through on it making practical on large data.that too for office(in limited time) is very danger...But by your suggestion, i get to know about partition. Thank you very much. :)quote:
Originally posted by RickD
Read the link, don't just dismiss something that will fix your problem out of hand because you are just a .Net developer.You have come on a SQL Server site to ask a SQL Server question.
G. Satish |
|
|
|
|
Can i move data instead of copy