Split Ms Sql 2005 Table to two or more parts

Question

I need to split an existing table in to two or more tables on Sql Server 2005. The table already has more than a thousand of rows.

For eg current table has cols A, B, C, D, E plus an id column. An I need to add A, B, C rows to another table in another database and add D, E to another table in another database.

I know that it is weird. But I really have to do this.

What is the best approach?

Answer 1

A thousand rows is hardly anything. I'm sure even SQL Server can handle millions.

But if you wish to go ahead, you could use the following psuedo code.

INSERT INTO SecondTable
SELECT id, A, B, C
FROM CurrentTable

INSERT INTO ThirdTable
SELECT id, D, E
FROM CurrentTable

This code assumes your 2nd and 3rd tables are empty. If they're not and you need to match on id's (say) it shouldn't be hard to extend the above code to handle this case.

Answer 2

I'd rather see you implement table partitions if scalability is really your goal.

Partitioned Tables and Indexes in SQL Server 2005

Easy Table Partitions with SQL Server 2008