問題描述
我要將一些數據從一個數據庫轉儲到另一個數據庫.我正在使用
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.
這篇關于沒有列名列表的tsql身份插入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！