Skip to content

Commit ad093de

Browse files
drumstadrumsta
committed
Update README.md
1 parent 6d192c3 commit ad093de

File tree

1 file changed

+198
-1
lines changed

1 file changed

+198
-1
lines changed

‎README.md

Lines changed: 198 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,199 @@
1-
# sql-generate-insert
1+
# INSERT statement(s) generator #
22
Generates INSERT statement(s) for data in a table.
3+
4+
## Purpose ##
5+
- To regenerate data at another location.
6+
- To script table or view data populated in automated way.
7+
- To script setup data populated in automated/manual way.
8+
9+
## Download and build instructions: ##
10+
11+
* Pre-requisites: MS SQL Server 2008 or later
12+
* Download a copy of the `GenerateInsert.sql`
13+
* Open SQL Server Management studio and load `GenerateInsert.sql`
14+
* Select a database to install the stored procedure to
15+
* Click Execute from the toolbar, this should run with a result of `Command Completely Successfully`
16+
17+
## Usage ##
18+
19+
### Quick example ###
20+
21+
```
22+
USE [AdventureWorks];
23+
GO
24+
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';
25+
```
26+
This will generate the following script:
27+
```
28+
SET NOCOUNT ON
29+
SET IDENTITY_INSERT Person.AddressType ON
30+
INSERT INTO Person.AddressType
31+
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
32+
VALUES
33+
(1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
34+
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
35+
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
36+
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
37+
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
38+
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
39+
SET IDENTITY_INSERT Person.AddressType OFF
40+
```
41+
42+
### Example using SELECT syntax ###
43+
44+
```
45+
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType'
46+
,@UseSelectSyntax=1
47+
,@UseColumnAliasInSelect=1
48+
,@GenerateOneColumnPerLine=1;
49+
```
50+
This will generate the following script:
51+
```
52+
SET NOCOUNT ON
53+
SET IDENTITY_INSERT Person.AddressType ON
54+
INSERT INTO Person.AddressType
55+
([AddressTypeID]
56+
,[Name]
57+
,[rowguid]
58+
,[ModifiedDate]
59+
)
60+
SELECT 1 [AddressTypeID]
61+
,N'Billing' [Name]
62+
,'B84F78B1-4EFE-4A0E-8CB7-70E9F112F886' [rowguid]
63+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
64+
UNION
65+
SELECT 2 [AddressTypeID]
66+
,N'Home' [Name]
67+
,'41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2' [rowguid]
68+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
69+
UNION
70+
SELECT 3 [AddressTypeID]
71+
,N'Main Office' [Name]
72+
,'8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575' [rowguid]
73+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
74+
UNION
75+
SELECT 4 [AddressTypeID]
76+
,N'Primary' [Name]
77+
,'24CB3088-4345-47C4-86C5-17B535133D1E' [rowguid]
78+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
79+
UNION
80+
SELECT 5 [AddressTypeID]
81+
,N'Shipping' [Name]
82+
,'B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5' [rowguid]
83+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
84+
UNION
85+
SELECT 6 [AddressTypeID]
86+
,N'Archive' [Name]
87+
,'A67F238A-5BA2-444B-966C-0467ED9C427F' [rowguid]
88+
,CONVERT(datetime,'2002-06-01 00:00:00.000',121) [ModifiedDate]
89+
SET IDENTITY_INSERT Person.AddressType OFF
90+
```
91+
92+
### Select results into table variable for later reuse ###
93+
```
94+
DECLARE @Results table (TableRow varchar(max));
95+
DECLARE @sql nvarchar(max) =
96+
'SELECT * FROM OPENROWSET (
97+
''SQLNCLI'',
98+
''Server=(local);Database='+(SELECT DB_NAME())+';Trusted_Connection=yes;'',
99+
''EXECUTE dbo.GenerateInsert @ObjectName = N''''Person.AddressType''''
100+
,@OmmitInsertColumnList=1
101+
,@GenerateSingleInsertPerRow=1
102+
,@FormatCode=0
103+
,@GenerateGo=0
104+
,@PrintGeneratedCode=0
105+
;''
106+
)';
107+
108+
INSERT INTO @Results
109+
EXECUTE sp_executesql @sql;
110+
111+
SELECT *
112+
FROM @Results;
113+
```
114+
This will generate the following script:
115+
```
116+
SET NOCOUNT ON
117+
SET IDENTITY_INSERT Person.AddressType ON
118+
INSERT INTO Person.AddressType VALUES (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
119+
INSERT INTO Person.AddressType VALUES (2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
120+
INSERT INTO Person.AddressType VALUES (3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
121+
INSERT INTO Person.AddressType VALUES (4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
122+
INSERT INTO Person.AddressType VALUES (5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
123+
INSERT INTO Person.AddressType VALUES (6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
124+
SET IDENTITY_INSERT Person.AddressType OFF
125+
```
126+
127+
## Arguments ##
128+
```
129+
@ObjectName
130+
Format: [schema_name.]object_name
131+
Specifies the name of a table or view to generate the INSERT statement(s) for
132+
@TargetObjectName
133+
Specifies the name of target table or view to insert into
134+
@OmmitInsertColumnList
135+
When 0 then syntax is like INSERT INTO object (column_list)...
136+
When 1 then syntax is like INSERT INTO object...
137+
@GenerateSingleInsertPerRow bit = 0
138+
When 0 then only one INSERT statement is generated for all rows
139+
When 1 then separate INSERT statement is generated for every row
140+
@UseSelectSyntax bit = 0
141+
When 0 then syntax is like INSERT INTO object (column_list) VALUES(...)
142+
When 1 then syntax is like INSERT INTO object (column_list) SELECT...
143+
@UseColumnAliasInSelect bit = 0
144+
Has effect only when @UseSelectSyntax = 1
145+
When 0 then syntax is like SELECT 'value1','value2'
146+
When 1 then syntax is like SELECT 'value1' column1,'value2' column2
147+
@FormatCode bit = 1
148+
When 0 then no Line Feeds are generated
149+
When 1 then additional Line Feeds are generated for better readibility
150+
@GenerateOneColumnPerLine bit = 0
151+
When 0 then syntax is like SELECT 'value1','value2'...
152+
or VALUES('value1','value2')...
153+
When 1 then syntax is like
154+
SELECT
155+
'value1'
156+
,'value2'
157+
...
158+
or VALUES(
159+
'value1'
160+
,'value2'
161+
)...
162+
@GenerateGo bit = 0
163+
When 0 then no GO commands are generated
164+
When 1 then GO commands are generated after each INSERT
165+
@PrintGeneratedCode bit = 1
166+
When 0 then generated code will be printed using PRINT command
167+
When 1 then generated code will be selected using SELECT statement
168+
@TopExpression varchar(max) = NULL
169+
When supplied then INSERT statements are generated only for TOP rows
170+
Format: (expression) [PERCENT]
171+
Example: @TopExpression='(5)' is equivalent to SELECT TOP (5)
172+
Example: @TopExpression='(50) PERCENT' is equivalent to SELECT TOP (5) PERCENT
173+
@SearchCondition varchar(max) = NULL
174+
When supplied then specifies the search condition for the rows returned by the query
175+
Format: <search_condition>
176+
Example: @SearchCondition='column1 != ''test''' is equivalent to WHERE column1 != 'test'
177+
@OmmitUnsupportedDataTypes bit = 1
178+
When 0 then error is raised on unsupported data types
179+
When 1 then columns with unsupported data types are excluded from generation process
180+
@PopulateIdentityColumn bit = 1
181+
When 0 then identity columns are excluded from generation process
182+
When 1 then identity column values are preserved on insertion
183+
@PopulateTimestampColumn bit = 0
184+
When 0 then rowversion/timestamp column is inserted using DEFAULT value
185+
When 1 then rowversion/timestamp column values are preserved on insertion,
186+
useful when restoring into archive table as varbinary(8) to preserve history
187+
@PopulateComputedColumn bit = 0
188+
When 0 then computed columns are excluded from generation process
189+
When 1 then computed column values are preserved on insertion,
190+
useful when restoring into archive table as scalar values to preserve history
191+
@ShowWarnings bit = 1
192+
When 0 then no warnings are printed.
193+
When 1 then warnings are printed if columns with unsupported data types
194+
have been excluded from generation process
195+
Has effect only when @OmmitUnsupportedDataTypes = 1
196+
@Debug bit = 0
197+
When 0 then no debug information are printed.
198+
When 1 then constructed SQL statements are printed for later examination
199+
```

0 commit comments

Comments
 (0)