The code below creates a temporary procedure called #usp_CreateAndInsert. Temporary procedures can be difficult animals, and you can see my experiences in trying to make this work in my last blog entry. I created a temporary procedure because I didn’t want to force a person to create my procedure in his/her own database but instead somewhere temporary where they don’t have to worry about cleaning up after themselves.
Currently this procedure only works for SQL2005 and beyond. It makes use of the sys.columns catalog view, which apparently wasn’t available in SQL2000. Depending on demand, I may amend the procedure to work in SQL2000.
To use this utility, just follow these steps:
Step 1: Set the current database to the source of the data. For example: USE AdventureWorks
Step 2: Copy/paste the huge chunk of code at the end of this blog entry into a new query window in SSMS and execute it by choosing Query -> Execute from the menu or hitting the F5 Key. It will create the temporary procedure called #usp_CreateAndInsert.
Step 3: Clear the same query window you're already in (don't open another query window because it won't be able to "see" the temporary procedure) and call the newly-created procedure with appropriate parameters. For example:
exec #usp_CreateAndInsert 'Sales.SalesReason'Step 4: That will produce the following result (I suggest you set your results to go to Grid rather than Text):
--Create/Populate [#SalesReason]:Step 5: Just do a Ctrl+A,Ctrl+C (Select All, Copy) in the results window to put that code on your clipboard and you’re ready to paste it (Ctrl+V) into a file or into a forum message.
if object_id('tempdb..[#SalesReason]','U') is not null drop table [#SalesReason]
go
create table [#SalesReason]
(
[SalesReasonID] int
,[Name] nvarchar(50)
,[ReasonType] nvarchar(50)
,[ModifiedDate] datetime
)
go
set nocount on
insert [#SalesReason] select 1, N'Price', N'Other', '1998-06-01T00:00:00'
insert [#SalesReason] select 2, N'On Promotion', N'Promotion', '1998-06-01T00:00:00'
insert [#SalesReason] select 3, N'Magazine Advertisement', N'Marketing', '1998-06-01T00:00:00'
insert [#SalesReason] select 4, N'Television Advertisement', N'Marketing', '1998-06-01T00:00:00'
insert [#SalesReason] select 5, N'Manufacturer', N'Other', '1998-06-01T00:00:00'
insert [#SalesReason] select 6, N'Review', N'Other', '1998-06-01T00:00:00'
insert [#SalesReason] select 7, N'Demo Event', N'Marketing', '1998-06-01T00:00:00'
insert [#SalesReason] select 8, N'Sponsorship', N'Marketing', '1998-06-01T00:00:00'
insert [#SalesReason] select 9, N'Quality', N'Other', '1998-06-01T00:00:00'
insert [#SalesReason] select 10, N'Other', N'Other', '1998-06-01T00:00:00'
go
--select * from [#SalesReason]
Step 6: Repeat steps 3-5 above to produce CREATE/INSERT code for other data.
Below is the code to create the #usp_CreateAndInsert procedure. I hope you find it useful. It will certainly be useful to people trying to help out on the forums.
if object_id('tempdb..#usp_CreateAndInsert','P') is not null
drop procedure #usp_CreateAndInsert
go
create procedure #usp_CreateAndInsert
@Table nvarchar(500) = null --The source table of the data
,@Select nvarchar(4000) = '*' --Columns to SELECT from the table
,@Join nvarchar(4000) = '' --Specify optional JOINs
,@Where nvarchar(4000) = '1=1' --Specify an optional WHERE clause
,@MaxRows bigint = 100 --Specify maximum records allowed
as
begin
/*
=====================================================================================
Procedure #usp_CreateAndInsert
Written by Brad Schulz (be_ess@yahoo.com) Jul2009
Description/Purpose:
Produces the commands necessary to create and populate a temporary table with
sample data.
The primary purpose is to provide an easy way for forum users to post the code
necessary for others to get a sampling of their data in order to help.
The output is just a list of rows (one command line per row) in the results window
of SSMS. Once produced, you can do a CTRL+A CTRL+C (Select All, Copy) in the
results window and then Paste (CTRL+V) the code into a file or in a message at
a SQL forum. (For best results in formatting and copying, set your Results to
Grid (as opposed to Text)).
Parameters:
@Table (required)
Supply the name of your source table in this parameter. It can be a simple
table name or a 2-part or 3-part or 4-part name. The final code that will
be produced will be create/insert code for a temporary table with the same name.
For example, if 'AdventureWorks.Sales.SalesOrderDetail' is supplied for @Table,
then the resulting create/insert code will be for a temporary table called
#SalesOrderDetail. If you pass the name of a temporary table already
(for example #Temp) then the resulting create/insert code will be for a table
of the same name (#Temp).
@Select (optional... defaults to '*')
Most likely you will want to limit the columns of the sample. Enter a comma-
delimited list of the columns to be pulled from the table specified by @Table.
IMPORTANT: Only columns the following datatypes will be included and all others
will be ignored: bigint, bit, decimal, int, money, numeric, smallint, smallmoney,
tinyint, float, real, date, datetime, datetime, datetimeoffset, smalldatetime,
time, char, varchar, nchar, nvarchar, uniqueidentifier.
Note that if a uniqueidentifier field is included, the resulting code will just
define it as a NVARCHAR field.
@Join (optional... defaults to '')
If you want to JOIN any tables to the table specified in the @Table parameter,
you can specify a JOIN clause (or clauses) here. See examples below for details.
(Note that if you do JOIN any other tables, you should specify @Select columns
and those columns should have aliases that match the table specified in the
@Table parameter.
@Where (optional... defaults to '1=1')
This allows you to limit the rows of the sample. Enter valid WHERE predicates.
See examples below for details.
@MaxRows (optional... defaults to 100)
This is a precaution to make sure you don't create a final output that is
thousands of lines long. If the selection of rows that you make exceeds
@MaxRows, then an error will occur. If you really need the number of rows to
be larger than the default of 100, then pass a different number for this
parameter.
Examples (assuming AdventureWorks database):
Number 1:
Produce Create/Insert statements for all fields in Sales.SalesReason table:
exec #usp_CreateAndInsert 'Sales.SalesReason'
Number 2:
Produce Create/Insert statements for a few fields in Sales.SalesPerson:
exec #usp_CreateAndInsert
'Sales.SalesPerson'
,@Select='SalesPersonID,SalesQuota,SalesYTD'
Number 3:
Produce Create/Insert statements for certain rows in Sales.Customer
exec #usp_CreateAndInsert
'Sales.Customer'
,@Where='TerritoryID=7 and CustomerType=''S'''
Note that you can use double quotation marks (") if you don't like having to
double up your single quotes, but this only works if the @Where parameter is
less than 128 chars:
exec #usp_CreateAndInsert
'Sales.Customer'
,@Where="TerritoryID=7 and CustomerType='S'"
Number 4:
Produce Create/Insert statements for a few fields in Sales.SalesOrderHeader,
and, in joining that table to Sales.Customer, filter out rows based on
fields in Sales.Customer:
exec #usp_CreateAndInsert
'Sales.SalesOrderHeader'
,@Select='SalesOrderID,OrderDate,PurchaseOrderNumber,Status'
,@Join='join Sales.Customer
on SalesOrderHeader.CustomerID=Customer.CustomerID'
,@Where='Customer.CustomerID<80 and
Customer.TerritoryID=7 and
Customer.CustomerType=''S'''
Or, alternately, you can use aliases:
exec #usp_CreateAndInsert
'Sales.SalesOrderHeader'
,@Select='SalesOrderID,OrderDate,PurchaseOrderNumber,Status'
,@Join='oh join Sales.Customer c on oh.CustomerID=c.CustomerID'
,@Where="c.CustomerID<80 and c.TerritoryID=7 and c.CustomerType='S'"
To be honest, it may be much easier to create a temp table that contains the
data you want and then just run this procedure off of the temp table:
select SalesOrderID,OrderDate,PurchaseOrderNumber,Status
into #SOH
from Sales.SalesOrderHeader oh
join Sales.Customer c on oh.CustomerID=c.CustomerID
where c.CustomerID<80 and c.TerritoryID=7 and c.CustomerType='S'
exec #usp_CreateAndInsert '#SOH'
Number 5:
Produce Create/Insert statements for a few fields in Sales.SalesOrderDetail,
and, in joining that table to Sales.SalesOrderHeader and Sales.Customer,
filter out rows based on fields in Sales.Customer:
exec #usp_CreateAndInsert
'Sales.SalesOrderDetail'
,@Select='od.SalesOrderID,od.SalesOrderDetailID,od.OrderQty,od.ProductID'
,@Join='od join Sales.SalesOrderHeader oh
on od.SalesOrderID=oh.SalesOrderID
join Sales.Customer c
on oh.CustomerID=c.CustomerID'
,@Where="c.CustomerID<80 and c.TerritoryID=7 and c.CustomerType='S'"
To be honest, it may be much easier to create a temp table that contains the
data you want and then just run this procedure off of the temp table:
select od.SalesOrderID,od.SalesOrderDetailID,od.OrderQty,od.ProductID
into #SOD
from Sales.SalesOrderDetail od
join Sales.SalesOrderHeader oh on od.SalesOrderID=oh.SalesOrderID
join Sales.Customer c on oh.CustomerID=c.CustomerID
where c.CustomerID<80 and c.TerritoryID=7 and c.CustomerType='S'
exec #usp_CreateAndInsert '#SOD'
=====================================================================================
*/
set nocount on
declare @EmptyString nchar(1)
,@Asterisk nchar(1)
,@TicTacToe nchar(1)
,@SqlStmt nvarchar(4000)
,@NumRows bigint
,@ErrMsg nvarchar(4000)
,@DestTable nvarchar(100)
,@ColValues nvarchar(4000)
set @EmptyString=''
set @Asterisk='*'
set @TicTacToe='#'
if isnull(@Table,@EmptyString)=@EmptyString
begin
raiserror('You must pass a @Table parameter.',16,1)
return -1
end
if @Select=@Asterisk and @Join<>@EmptyString
begin
set @ErrMsg=
'You must specify columns in the @Select parameter '
+'if you pass something in the @Join parameter.'
raiserror(@ErrMsg,16,1)
return -1
end
set @SqlStmt=
'select @NumRows=count(*) from '+@Table+' '+@Join+' where '+@Where
--print @SqlStmt
exec sp_executesql @SqlStmt, N'@NumRows bigint output', @NumRows output
if @@error<>0
begin
set @ErrMsg=
'The @Table, @Join, @Where parameters are invalid.'+char(13)
+'The SQL Statement attempted was the following:'+char(13)
+' select count(*)'+char(13)
+' from '+@Table+' '+@Join+char(13)
+' where '+@Where
raiserror(@ErrMsg,16,1)
return -1
end
if @NumRows>@MaxRows
begin
set @ErrMsg=
'Your selection produces '+ltrim(str(@NumRows))+' rows, '
+'which is more than the '+ltrim(str(@MaxRows))+' maximum allowed.'+char(13)
+'If you want more than the maximum, then use the @MaxRows parameter'
raiserror(@ErrMsg,16,1)
return -1
end
if object_id('tempdb..##_CAI_Data','U') is not null drop table ##_CAI_Data
--Acquire the data and put into a (global) temporary table called ##_CAI_Data.
--The reason I use a global temp table is because that's the only kind of temp table
--that can be created by sp_executesql that can be used again by this procedure.
set @SqlStmt=
'select '+@Select+' into ##_CAI_Data from '+@Table+' '+@Join+' where '+@Where
--print @SqlStmt
exec sp_executesql @SqlStmt
if @@error<>0
begin
set @ErrMsg=
'The @Table, @Select, @Join, @Where parameters are invalid.'+char(13)
+'The SQL Statement attempted was the following:'+char(13)
+' select '+@Select+char(13)
+' from '+@Table+' '+@Join+char(13)
+' where '+@Where
raiserror(@ErrMsg,16,1)
return -1
end
--Now that we've gotten our sample data into ##_CAI_Data, let's pull out the
--column definitions of that table and put that information into another
--temporary table called #_CAI_Stru
if object_id('tempdb..#_CAI_Stru','U') is not null drop table #_CAI_Stru
select Name=Name
,DataType=type_name(System_Type_ID)
,Max_Length
,Precision
,Scale
,Collation_Name=Collation_Name
,Column_ID
into #_CAI_Stru
from tempdb.sys.columns
where Object_ID=object_id('tempdb..##_CAI_Data','U')
and type_name(System_Type_ID) in ('bigint','bit','decimal','int','money','numeric'
,'smallint','smallmoney','tinyint'
,'float','real'
,'date','datetime2','datetime','datetimeoffset'
,'smalldatetime','time'
,'char','varchar','nchar','nvarchar'
,'uniqueidentifier')
--Create the code that will eventually pull that actual data
--out of our #_CAI_Data table
set @ColValues=''
select @ColValues=
@ColValues
+case
when @ColValues=@EmptyString
then ''
else '+'', '''+char(13)+space(11)+'+'
end
+'coalesce('+
+case
when DataType in ('char','varchar')
then 'quotename('+quotename(Name)+','''''''')'
when DataType in ('nchar','nvarchar')
then '''N''+quotename('+quotename(Name)+','''''''')'
when DataType in ('date')
then '''''''''+convert(nvarchar(100),'+quotename(Name)+',112)+'''''''''
when DataType in ('time')
then '''''''''+convert(nvarchar(100),'+quotename(Name)+',108)+'''''''''
when DataType in ('datetime','datetime2','smalldatetime','datetimeoffset')
then '''''''''+convert(nvarchar(100),'+quotename(Name)+',126)+'''''''''
when DataType in ('money','smallmoney','float','real')
then 'convert(nvarchar(100),'+quotename(Name)+',2)'
when DataType in ('uniqueidentifier')
then '''''''''+convert(nvarchar(100),'+quotename(Name)+')+'''''''''
else 'convert(nvarchar(100),'+quotename(Name)+')'
end
+',''NULL'')'
from #_CAI_Stru
--print @ColValues
--Set the destination table that will come out in the resulting code
--to be a temp table name. Note how PARSENAME() is used to get the
--actual table name of whatever was passed in the @Table parameter
--and a '#' is placed before that to get the destination table.
set @DestTable=case
when parsename(@Table,1) is not null
then parsename(@Table,1)
when parsename(@Table,2) is not null
then parsename(@Table,2)
when parsename(@Table,3) is not null
then parsename(@Table,3)
else @Table
end
set @DestTable=quotename(case when left(@DestTable,1)=@TicTacToe
then ''
else '#'
end+@DestTable)
--Put all the statements together into one giant UNIONed query which
--we will execute via sp_executesql
set @SqlStmt='
select ''--Create/Populate '+@DestTable+':'' collate database_default
union all
select ''if object_id(''''tempdb..'+@DestTable+''''',''''U'''') is not null ''
+''drop table '+@DestTable+'''
union all
select ''go''
union all
select ''create table '+@DestTable+'''
union all
select ''(''
union all
select '' ''+case when FirstLine=1 then '' '' else '','' end+ColDef
from
(
select ColDef=
quotename(Name)+'' ''
+case
when DataType=''uniqueidentifier''
then ''nvarchar(50)''
else DataType
end
+case
when DataType in (''char'',''varchar'',''binary'',''varbinary'')
then ''(''+case when Max_Length=-1
then ''max''
else convert(varchar,Max_Length) end+'')''
when DataType in (''nchar'',''nvarchar'')
then ''(''+case when Max_Length=-1
then ''max''
else convert(varchar,Max_Length/2) end+'')''
when DataType in (''decimal'',''numeric'')
then ''(''+convert(varchar,Precision)+'',''+convert(varchar,Scale)+'')''
when DataType in (''float'')
then ''(''+convert(varchar,Precision)+'')''
else ''''
end
,FirstLine=case when Column_ID=(select min(Column_ID) from #_CAI_Stru)
then 1
else 0 end
from #_CAI_Stru
) ColInserts
union all
select '')''
union all
select ''go''
union all
select ''set nocount on''
union all
select ''insert '+@DestTable+' select ''
+'+@ColValues+'
from ##_CAI_Data
union all
select ''go''
union all
select ''--select * from '+@DestTable+'''
'
--print @SqlStmt
exec sp_executesql @SqlStmt
if @@error<>0
begin
raiserror('Unknown error in executing final SQL Statement.',16,1)
return -1
end
drop table ##_CAI_Data
drop table #_CAI_Stru
end
Posts
Great post, thanks
ReplyDeleteBrad,
ReplyDeleteInstead of returning an error when Number of rows exceed the @MaxRows, would it be possible to arbitrary limit the number of rows returned (by using select top (@MaxRows) )?
See
http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/eef346f9-394f-40f6-bc70-a23ebd73dfcc
This was great to read, thanks
ReplyDelete