在SQL Server 2008中合并两个表的最佳方式

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

我试图将两个类似的表(不准确)合并到一个表中。有很多错误，有什么好办法吗?

3 个解决方案

#1

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.

我看到tblDVPage中没有保存tbldvpagecegory的类型字段。输入，如果您想将后者合并到前者，那么您可能需要为它创建一个新的列，或者保存tblDVpagecategory。与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.

显然，您在tblDVpagecategory中的旧id将会消失，并在将这些项合并到tblDVPage时重新生成。如果你想用一个新的列来区分什么是什么，这取决于你的场景。

#2

Have you considered:

你考虑过:

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
创建一个视图来连接两个表，其中一个where子句限制在视图设计过程中更快执行的行数。
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
视图正确后，使用where条件1=2执行“select into”。这将为您提供一个空表，其中包含视图中的列名和数据类型
Edit the table design to include identity column, adjust data types, column names, etc
编辑表设计以包含标识列、调整数据类型、列名等
Insert data from the view into the table
将视图中的数据插入到表中
Create indexes and FK as necessary
根据需要创建索引和FK
Rename tables if necessary
重命名表如果有必要
Drop the view
减少视图

Beware of transaction log growth

注意事务日志的增长

#3

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.

我很困惑你在SQL Server 2005上做了标记，但你问的是2008年?如果您确实在使用2008，请尝试使用MERGE (Transact-SQL)，后者仅在SQL Server 2008上可用。

#1