SQL Server 2005 - Move data between Databases

[cumpleby]

Hi, im looking at Moving data older than 120 days from one database Table to another database Table. I have tested using the Select * INTO table name from OriginalTable where FieldDate < dateadd(day, -120, getdate()). This inserts the data over 120 days old into the Table on the 2nd Database but keeps the data in the original table. What im looking for is a move command so it moves the data from the Original Table to the New Table, is this possible (Im trying to keep anyway from the DELETE command as ive heard stories about this affecting the stats and indexes on the other tables on the database)? I Hope someone can help me out there.

Thanks

 

[TysonLPrice]

I don't think you can "Move" table data around like that (where the move pshycially deleted the data). You will have to maintain it yourself. Delete is an integral part of SQL. What issues with that concern you?

 

[SQLBill]

Also, SELECT * INTO only works once per table. Unless you are creating a new table everytime, you will need to change it to be INSERT INTO. SELECT * INTO creates the table and puts the data into it, but if the table exists then SELECT * INTO fails with the error that the table already exists.

And about DELETE....if you update data SQL Server is really doing a DELETE and INSERT, so you are already deleting data (you just put it back with new values).


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Bill, are you sure that's true? I don't think it is.

Regarding the deletes, the problem is that SQL server will not reduce the size of the database and will leave "holes" all though out your data. You cannot get around the issue, however, you should rebuild your indexes after deleting a lot of data. This will minimize the storage requirements as well as optimizing the data for retrieval later.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George, I agree with SQLBill. After having forgotten and using a "SELECT * INTO" that I already had I get a nice error message. I have learned to drop the table first.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I apologize for not being clearer. I know that "Select Into" only works if the table you are selecting in to does not already exist because it creates the table.

My comment was directed towards:

And about DELETE....if you update data SQL Server is really doing a DELETE and INSERT, so you are already deleting data (you just put it back with new values).

I would agree that it appears this way when you are working with triggers because of the pseudo tables Inserted and Deleted, but I don't think SQL Server actually deletes the row and inserts it when you make a change (update statement). Anyway... this is the part I was questioning. If there is any documentation regarding this, I would be interested in seeing it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

I'll see if I can find it George...I know that's how I've always been taught and in replication I've seen two transactions when there is an update being replication (delete and insert). But let me look for actual documentation.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TysonLPrice]

I always thought an update was a delete and insert but looking around I'm seeing "it depends". I see that is this post:

http://dbaspot.com/ms-sqlserver/149740-how-does-update-statement-work.html

In that post is reference to a book with more detailed information but I don't see it anywhere for free.

"Inside SQL Server 2005: The Storage Engine by Kalen Delanay"

I can't seem to find a definitive answer to that specific question though.