Moving data from one table to another

[bthumber]

How do you move data from one table to another using a insert statement? The
tables are be in the same database.

 

[Gregory A. Beamer]

SELECT * INTO Table2 FROM Table1;
DELETE FROM Table1;

Mark's suggestion (above) is great for the initial move. After that, you
can use something like:

INSERT INTO Table2
SELECT * FROM Table1;

If there is an identity column in Table2, you will have to surround the
statement with IDENTITY_INSERT on. Something like:

SET IDENTITY_INSERT Table2 ON

INSERT INTO Table2 (col1, col2, col3, col4, colN)
SELECT * FROM Table1
WHERE Date < '1/1/2009'

SET IDENTITY_INSERT Table2 ON
GO

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************