Best way to Merge 2 tables in SQL Server 2008

I am trying to merge two similar 开发者_JAVA技巧tables (not exact) to one table. Getting lots of errors Any ideas on best way to do this?

I see there is not a Type field in tblDVPage to hold tblDVpagecategory.type, if you wanted to merge the latter into the former, so you may need to create a new column for it, or hold tblDVpagecategory.type with tblDVPage.pageType.

Either ways you will end up having something similar to this:

INSERT INTO tblDVPage(Title, ParentId, PageType, MenuOrder)
SELECT Title, ParentId, Type, MenuOrder
FROM tblDVpagecategory

Obviously your old IDs in tblDVpagecategory will be gone, and regenerated when the items are merged into tblDVPage. If you might want to differentiate what is what with a new column, but depends on your scenario.

Have you considered:

1. Create a view to join the two tables with a where clause limiting the number of rows for quicker execution during the design of the view
2. After the view is correct do a "select into" with a where condition of 1=2. This will give you an empty table with column names and data types from the view
3. Edit the table design to include identity column, adjust data types, column names, etc
4. Insert data from the view into the table
5. Create indexes and FK as necessary
6. Rename tables if necessary
7. Drop the view

Beware of transaction log growth

I'm confused you tag the question SQL Server 2005, but you ask about 2008? If you are really using 2008, try using MERGE (Transact-SQL), which is only available on SQL Server 2008.