Cool Insert Generator

Introduction

Writing an insert stored procedure in SQL for a giant table is a hard job. You have to hardcode all the tedious code, you should declare input parameters for your SP and then select all those columns in INSERT VALUES command and pass in all those parameters again. This stored procedure can be used for creating those kind of stored procedures. It can do all the hard work for you.

Background

Intermediate knowledge of T-SQL would be enough.

Using the Code

After creating the CoolInsertGenerator stored procedure (link on the stored procedure is available at the end of the tip), you can execute it for any table in your database. For example, you can execute it for Production.Product in AdventureWorks database as follows:

EXEC USP_CoolInsertGenerator 'Production' , 'Product' , @ShowOutput = 0 

And it will generate the following stored procedure for you:

 USE [AdventureWorks2012]
GO
/****** Object:  StoredProcedure [dbo].[USP_Product_Insert]    Script Date: 9/25/2013 3:14:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[USP_Product_Insert] 
/**********************************************************************************************************
PURPOSE: Template 
NOTES:  
Change History: 
Date		| Author				| Description     
2013-09-25	| Cool Insert Generator			| Created by Cool Insert Generator
**********************************************************************************************************/ 
@Name nvarchar(50)
	,@ProductNumber nvarchar(25)
	,@MakeFlag bit
	,@FinishedGoodsFlag bit
	,@Color nvarchar(15)
	,@SafetyStockLevel smallint
	,@ReorderPoint smallint
	,@StandardCost money
	,@ListPrice money
	,@Size nvarchar(5)
	,@SizeUnitMeasureCode nchar(3)
	,@WeightUnitMeasureCode nchar(3)
	,@Weight decimal
	,@DaysToManufacture int
	,@ProductLine nchar(2)
	,@Class nchar(2)
	,@Style nchar(2)
	,@ProductSubcategoryID int
	,@ProductModelID int
	,@SellStartDate datetime
	,@SellEndDate datetime
	,@DiscontinuedDate datetime
	,@ModifiedDate datetime
 AS BEGIN 
INSERT Production.Product(
[Name]
	,[ProductNumber]
	,[MakeFlag]
	,[FinishedGoodsFlag]
	,[Color]
	,[SafetyStockLevel]
	,[ReorderPoint]
	,[StandardCost]
	,[ListPrice]
	,[Size]
	,[SizeUnitMeasureCode]
	,[WeightUnitMeasureCode]
	,[Weight]
	,[DaysToManufacture]
	,[ProductLine]
	,[Class]
	,[Style]
	,[ProductSubcategoryID]
	,[ProductModelID]
	,[SellStartDate]
	,[SellEndDate]
	,[DiscontinuedDate]
	,[ModifiedDate]
)VALUES(
@Name
	,@ProductNumber
	,@MakeFlag
	,@FinishedGoodsFlag
	,@Color
	,@SafetyStockLevel
	,@ReorderPoint
	,@StandardCost
	,@ListPrice
	,@Size
	,@SizeUnitMeasureCode
	,@WeightUnitMeasureCode
	,@Weight
	,@DaysToManufacture
	,@ProductLine
	,@Class
	,@Style
	,@ProductSubcategoryID
	,@ProductModelID
	,@SellStartDate
	,@SellEndDate
	,@DiscontinuedDate
	,@ModifiedDate
) END


/**************************************
--  Average execution time: ( To be added after testing execution ) ms  
DECLARE @StartTime DATETIME 
SET @StartTime = GETDATE()  
EXEC USP_Product_Insert 
@Name = 'T'
	,@ProductNumber = 'T'
	,@MakeFlag = 1
	,@FinishedGoodsFlag = 1
	,@Color = 'T'
	,@SafetyStockLevel = 1
	,@ReorderPoint = 1
	,@StandardCost = 1
	,@ListPrice = 1
	,@Size = 'T'
	,@SizeUnitMeasureCode = 'T'
	,@WeightUnitMeasureCode = 'T'
	,@Weight = 1
	,@DaysToManufacture = 1
	,@ProductLine = 'T'
	,@Class = 'T'
	,@Style = 'T'
	,@ProductSubcategoryID = 1
	,@ProductModelID = 1
	,@SellStartDate = 'Sep 25 2013  3:14AM'
	,@SellEndDate = 'Sep 25 2013  3:14AM'
	,@DiscontinuedDate = 'Sep 25 2013  3:14AM'
	,@ModifiedDate = 'Sep 25 2013  3:14AM'

PRINT CAST(DATEDIFF ( ms, @StartTime, GETDATE() ) AS VARCHAR(MAX)) + ' (In ms)'

***************************************/ 

Points of Interest

Feel free to investigate and extend :). More information about the instructions is available on github.