為SQL Server表自動生成INSERT語句的最佳方法是什麼?




sql-server code-generation (13)

我們正在編寫一個新的應用程序,在測試時,我們需要一些虛擬數據。 我已經使用MS Access將這些數據添加到相關表格中。

每隔一段時間,我們都想“刷新”相關表格,這意味著全部刪除它們,重新創建它們,並運行已保存的MS Access追加查詢。

第一部分(刪除和重新創建)是一個簡單的sql腳本,但最後一部分讓我感到畏縮。 我想要一個包含一堆INSERT的安裝腳本來重新生成虛擬數據。

我現在有表格中的數據。 從該數據集自動生成大量INSERT語句的最佳方法是什麼?

我正在考慮類似於TOAD(對於Oracle),您可以右鍵單擊一個網格並單擊另存為 - >插入語句,它只會在您想要的任何位置轉儲大型SQL腳本。

我能想到的唯一方法是將表格保存到Excel表格中,然後編寫一個Excel公式來為每一行創建一個INSERT,這當然不是最好的方法。

我正在使用2008 Management Studio連接到SQL Server 2005數據庫。



sp_generate_inserts的第一個鏈接非常酷,這裡是一個非常簡單的版本:

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Queues'               -- your table

DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) + 
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '
PRINT @S'

EXEC (@SQL)

在我的系統上,我得到了這個結果:

INSERT INTO Queues([QueueName], [iSort])
SELECT 'WD: Auto Capture', '10' UNION 
SELECT 'Car/Lar', '11' UNION 
SELECT 'Scan Line', '21' UNION 
SELECT 'OCR', '22' UNION 
SELECT 'Dynamic Template', '23' UNION 
SELECT 'Fix MICR', '41' UNION 
SELECT 'Fix MICR (Supervisor)', '42' UNION 
SELECT 'Foreign MICR', '43' UNION 
...

不確定,如果我正確理解你的問題。

如果MS-Access中有數據需要將其移至SQL Server,則可以使用DTS。
而且,我想你可以使用SQL profiler來查看所有的INSERT語句。


不要使用插入物,使用BCP


你有生產數據庫中的數據嗎? 如果是這樣,您可以通過DTS設置數據的周期刷新。 我們每週都會在周末做我們的工作,我們每週都會為我們的測試提供乾淨,真實的數據,這是非常好的。

如果你還沒有產品,那麼你應該創建一個他們想要的數據庫(新鮮)。 然後,複製該數據庫並將該新創建的數據庫用作您的測試環境。 當你想要乾淨的版本,只需複制你的干淨的一個, 鮑勃的叔叔


如果您需要編程訪問,那麼您可以使用開源存儲過程GenerateInsert。

INSERT語句生成器

就像一個簡單而快速的例子一樣,為一個表AdventureWorks.Person.AddressType生成INSERT語句,執行以下語句:

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

這將生成以下腳本:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

您可以使用SSMS工具包(適用於SQL Server 2005和2008)。 它帶有一個用於生成插入語句的功能。

http://www.ssmstoolspack.com/


我也對此進行了很多研究,但我無法得到具體的解決方案。 目前我遵循的方法是從SQL Server Managment studio複製excel中的內容,然後將數據導入Oracle-TOAD,然後生成插入語句


我們使用這個存儲過程 - 它允許你定位特定的表,並使用where子句。 你可以在here找到文字。

例如,它可以讓你這樣做:為表'titles'生成INSERT語句:

EXEC sp_generate_inserts 'titles'

我對這個問題的貢獻是Powershell INSERT腳本生成器,它可以讓您腳本多個表而不必使用繁瑣的SSMS GUI。 非常適合將“種子”數據快速持久保存到源代碼控制中。

  1. 將下面的腳本保存為“filename.ps1”。
  2. 對“CUSTOMIZE ME”下的區域進行自己的修改。
  3. 您可以按任何順序將表格列表添加到腳本中。
  4. 您可以在Powershell ISE中打開腳本並點擊播放按鈕,或者只需在Powershell命令提示符下執行該腳本即可。

默認情況下,生成的INSERT腳本將與腳本位於同一文件夾下的“SeedData.sql”。

您將需要安裝SQL Server管理對象程序集,如果您安裝了SSMS,它們應該在那裡。

Add-Type -AssemblyName ("Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
Add-Type -AssemblyName ("Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")



#CUSTOMIZE ME
$outputFile = ".\SeedData.sql"
$connectionString = "Data Source=.;Initial Catalog=mydb;Integrated Security=True;"



$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn)
$db = $srv.Databases[$srv.ConnectionContext.DatabaseName]
$scr = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scr.Options.FileName = $outputFile
$scr.Options.AppendToFile = $false
$scr.Options.ScriptSchema = $false
$scr.Options.ScriptData = $true
$scr.Options.NoCommandTerminator = $true

$tables = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection



#CUSTOMIZE ME
$tables.Add($db.Tables["Category"].Urn)
$tables.Add($db.Tables["Product"].Urn)
$tables.Add($db.Tables["Vendor"].Urn)



[void]$scr.EnumScript($tables)

$sqlConnection.Close()

我用這個script放在我的博客上( 如何在sql server上生成插入語句過程 )。

到目前為止我已經為我工作,雖然他們可能是我還沒有發現的錯誤。


正如@Mike Ritacco所提到的,但更新為SSMS 2008 R2

  1. 右鍵單擊數據庫名稱
  2. 選擇任務>生成腳本
  3. 根據您的設置,介紹頁面可能會顯示或不顯示
  4. 選擇'選擇特定的數據庫對象',
  5. 展開樹視圖並檢查相關表格
  6. 點擊下一步
  7. 點擊高級
  8. 在“常規”部分下,選擇“適用於腳本的數據類型”
  9. 完成嚮導

然後,您將直接從SSMS中獲取數據的所有INSERT語句。

編輯2016-10-25 SQL Server 2016 / SSMS 13.0.15900.1

  1. 右鍵單擊數據庫名稱

  2. 選擇任務>生成腳本

  3. 根據您的設置,介紹頁面可能會顯示或不顯示

  4. 選擇'選擇特定的數據庫對象',

  5. 展開樹視圖並檢查相關表格

  6. 點擊下一步

  7. 點擊高級

  8. 在“常規”部分下,選擇“適用於腳本的數據類型”

  9. 點擊確定

  10. 選擇是否希望輸出轉到新的查詢,剪貼板或文件

  11. 點擊下一步兩次

  12. 您的腳本根據您在上面選擇的設置進行準備

  13. 點擊完成


這也可以使用Visual Studio來完成(至少在版本2013以後)。

在VS 2013中,也可以過濾 inserts語句所基於的行列表,這在SSMS中是不可能的,就我所知。

執行以下步驟:

  • 打開“SQL Server對象資源管理器”窗口(菜單:/ View / SQL Server對象資源管理器)
  • 打開/展開數據庫及其表格
  • 右鍵單擊表格並從上下文菜單中選擇“查看數據”
  • 這將顯示主要區域中的數據
  • 可選步驟:點擊過濾器圖標“排序和過濾數據集”(結果上方的行左側的第四個圖標),並將一些過濾器應用於一列或多列
  • 點擊“Script”或“Script to File”圖標(上排右側的圖標,它們看起來像小紙片)

這將為所選表創建(條件)插入語句到活動窗口或文件。


“過濾器”和“腳本”按鈕Visual Studio 2013





code-generation