[SSMS] 指令碼精靈

[SSMS] 產生 Script 以 Alter 為目標，紀錄單一物件和多物件產生 Script 方式，該篇會以指令碼精靈為主，指令碼精靈除了產生物件 Script 之外，還可以把資料一併轉出

DB => 工作 => 產生指令碼

這邊可以選擇 [編寫整個資料庫和所有資料庫物件的指令碼] 或 [選擇特定的資料庫物件]，該筆記就單選轉出 Person.Person Table 而已

把物件匯出成 Script 並點選右上角的 [進階] 選項 

進階選項內的 [要編寫指令碼的資料類型] 有三種選項，分別為

• 結構描述和資料
• 僅限結構描述
• 僅限資料

該筆記以 [結構描述和資料] 為主，要同時匯出 Table Scheam 和資料

轉出的 Script 語法，資料部分只記錄 5 筆資料

USE [AdventureWorks2022]
GO
/****** Object:  Table [Person].[Person]    Script Date: 2023/10/10 下午 10:32:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Person](
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] [dbo].[NameStyle] NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [dbo].[Name] NOT NULL,
	[MiddleName] [dbo].[Name] NULL,
	[LastName] [dbo].[Name] NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL,
	[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
	[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
	[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (1, N'EM', 0, NULL, N'Ken', N'J', N'Sánchez', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'92c4279f-1207-48a3-8448-4636514eb7e2', CAST(N'2009-01-07T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (2, N'EM', 0, NULL, N'Terri', N'Lee', N'Duffy', NULL, 1, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'd8763459-8aa8-47cc-aff7-c9079af79033', CAST(N'2008-01-24T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (3, N'EM', 0, NULL, N'Roberto', NULL, N'Tamburello', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'e1a2555e-0828-434b-a33b-6f38136a37de', CAST(N'2007-11-04T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (4, N'EM', 0, NULL, N'Rob', NULL, N'Walters', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'f2d7ce06-38b3-4357-805b-f4b6b71c01ff', CAST(N'2007-11-28T00:00:00.000' AS DateTime))
INSERT [Person].[Person] ([BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate]) VALUES (5, N'EM', 0, N'Ms.', N'Gail', N'A', N'Erickson', NULL, 0, NULL, N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>', N'f3a3f6b4-ae3b-430c-a754-9f2231ba6fef', CAST(N'2007-12-30T00:00:00.000' AS DateTime))

指令碼精雖然方便把資料轉出成 Script 來使用，但不建議使用就是，之前有過一萬筆左右 Table，在 Local 端透過指令碼精靈轉出，在離峰時間拿到 Product 環境上去進行 insert，花了快 10 分鐘才完成，有點出乎意外之外的慢，之後就乖乖的使用 bcp out 把資料倒出，bcp in 或 bulk insert 匯入資料比較快速

• 參考資料
• 產生和發佈指令碼
• 產生資料庫物件的T-SQL指令碼
• 將資料表的資料，產生成 Insert 陳述式：使用 SQL Server Management Studio 2008 指令碼精靈