没有列名列表的tsql身份插入
我要将一些数据从一个数据库转储到另一个数据库.我正在使用
I am going to dump some data from one db to another one. I am using
set identity_insert MyTable on
GO
INSERT INTO MyTable SELECT * FROM sourceDB.dbo.MyTable
GO
set identity_insert MyTable off
无论如何要让它发挥作用?有 30 个表，将列名列表添加到插入语句会很耗时.我使用的是 SQL server 2000.近期会升级到 SQL server 2008.
Is anyway to get this to work? There's 30 tables and it will be time consuming to add the list of column names to the insert statement. I am using SQL server 2000. Will be upgraded to SQL server 2008 in the near future.
MyTable 有一个标识列.
MyTable has an identity column.
推荐答案
只需从对象浏览器中拖放列名称即可.您可以执行一个步骤，它比编写 select * 需要大约 1 秒的时间，并且无论如何您都不应该在生产代码中使用 select *.这是一个糟糕的做法.
Just drag and drop the column names from the object browser. You can do it one step it takes about 1 second longer than writing select * and you should never use select * in production code anyway. It is a poor practice.
不过，我担心您插入 Identity 列，这是几乎永远不应该做的事情.如果原始表的某些标识列与新表中的现有 ID 相同，该怎么办?在决定从另一个表插入 id 值之前，请务必检查这一点.我更喜欢插入到父表并获得一个新的 id 并将其与旧的 id 匹配(2008 年的输出对此很好)，然后将新的 id 用于任何子表，但加入旧的 id.
I am concerned about you inserting Identity columns though, this is something that should almost never be done. What if the original table had some identity columns that are the same as existing ids in the new table? Make sure to check for this before deciding to insert id values from another table. I prefer to do the insert to the parent table and get a new id and match it to the old id (output is good for this in 2008) and then use the new id for any child tables but join on the old id.
相关文章