#usp_CreateAndInsert

People will post questions on T-SQL forums and sometimes will provide sample data, but the data is not provided in a way that others can readily use to create identical sample tables. It is admittedly kind of a pain to write out the code necessary to CREATE TABLE and to populate it via INSERT commands with meaningful data. And that’s where this utility comes in.

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]:if object_id('tempdb..[#SalesReason]','U') is not null drop table [#SalesReason]
gocreate table [#SalesReason](   [SalesReasonID] int  ,[Name] nvarchar(50)  ,[ReasonType] nvarchar(50)  ,[ModifiedDate] datetime)goset 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 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.

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
gocreate 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 allowedas
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)=@EmptyStringbegin  raiserror('You must pass a @Table parameter.',16,1)  return -1end
  
if @Select=@Asterisk and @Join<>@EmptyStringbegin  set @ErrMsg=        'You must specify columns in the @Select parameter '       +'if you pass something in the @Join parameter.'  raiserror(@ErrMsg,16,1)  return -1end
  
set @SqlStmt=      'select @NumRows=count(*) from '+@Table+' '+@Join+' where '+@Where--print @SqlStmtexec sp_executesql @SqlStmt, N'@NumRows bigint output', @NumRows output
if @@error<>0begin  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 -1end
if @NumRows>@MaxRowsbegin  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 -1end
  
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 @SqlStmtexec sp_executesql @SqlStmt
  if @@error<>0begin  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 -1end
  --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_Struif object_id('tempdb..#_CAI_Stru','U') is not null drop table #_CAI_Struselect Name=Name      ,DataType=type_name(System_Type_ID) 
      ,Max_Length
      ,Precision      ,Scale
      ,Collation_Name=Collation_Name 
      ,Column_IDinto #_CAI_Strufrom 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 tableset @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_executesqlset @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 @SqlStmtexec sp_executesql @SqlStmt
  if @@error<>0begin  raiserror('Unknown error in executing final SQL Statement.',16,1)  return -1end
  
drop table ##_CAI_Datadrop table #_CAI_Stru
  end