Alex A
asked on
SQL Server: how to MOVE data from one table to another?
Hi, I am wondering if there is a comand to move (not copy) data from one table to another. I've got a bunch of huge tables and inserting data into new tables and deleting from existing tables takes lots of time.
I there a way to point new tabes to the existing data, without duplicating data first?
I there a way to point new tabes to the existing data, without duplicating data first?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Move without copying - no, you can't do that.
Think about programming a simple program with variables 'foo' and 'bar'. If 'bar' contains the number '5' and you want to "move" that value to 'foo', you first set 'foo' to 5, then set 'bar' to undef. In effect, copy then delete.
If you are simply reading from these tables, you *can* create a VIEW that describes the shape of the data you want without having to create/copy/delete any data from any tables.
Are you moving the minimal set of data? Instead of copying your "big" tables, alter them and add in the additional columns from your new tables and then move the "smaller" data into them. In other words, you always leave your largest dataset alone and instead add onto in smaller datasets. As PortletPaul mentions, you can always rename your table if needed.
Think about programming a simple program with variables 'foo' and 'bar'. If 'bar' contains the number '5' and you want to "move" that value to 'foo', you first set 'foo' to 5, then set 'bar' to undef. In effect, copy then delete.
If you are simply reading from these tables, you *can* create a VIEW that describes the shape of the data you want without having to create/copy/delete any data from any tables.
Are you moving the minimal set of data? Instead of copying your "big" tables, alter them and add in the additional columns from your new tables and then move the "smaller" data into them. In other words, you always leave your largest dataset alone and instead add onto in smaller datasets. As PortletPaul mentions, you can always rename your table if needed.
http://blogs.msdn.com/b/cdnsoldevs/archive/2011/09/06/how-to-move-data-from-one-table-to-another.aspx
http://stackoverflow.com/questions/1612267/move-sql-data-from-one-table-to-another