SQL Feather and Quill

Here’s a script that will create an insert/update statement for a list of values.  This is a major time saver for me – I have to create scripts like this for people fairly often. It is designed for a simple look up table with an ID column and a some sort of description column such as this….

Create table TestAndysAwesomeInsertGenerator
	(TestID int,
	TestDesc varchar(50),
	TSTimestamp timestamp)

Now let’s say you needed to generate insert and/or update statements for this list of values like this:

1. This is line1
2. This is line2
3. This is line3
4. etc…

You could enter the data into a table, then script out the insert statements and worry about the update statements later – or create row update/insert and do a lot of copying and pasting and editing, etc.  Well here’s a shortcut I came up with…use this script that creates a script.

Set nocount on
--/*Declare the variables.*/
Declare @ScriptOutput varchar(Max),
	@tablename varchar(255),
	@Col1 varchar(255),
	@Col2 varchar(255),
	@Counter1 int,
	@Counter2 int,
	@DescValue varchar(255)

Declare @ValuesTable table ([ID] int IDENTITY(1,1), [Description] varchar(255))
Declare @FinalScript table ([Script] varchar(max))

----------------------------------------------------------------------------------------------
--/*  This is the section that needs to be changed each time for each table and the values. */
----------------------------------------------------------------------------------------------
Set @tablename = 'TestAndysAwesomeInsertGenerator'

Insert @ValuesTable
Values('This is line1'),
	('This is line2'),
	('This is line3'),
	('etc...')

--------------------------------------------------------------------------------------------
--/*  That's it - everything else should be automatic - if it's a simple table.   */
--------------------------------------------------------------------------------------------
Select @Counter1 = COUNT(*) from @ValuesTable
Set @Counter2 = 1

--/* if the table has more columns, hard code the column names in here. */
Select @Col1 = SC.name from sys.columns SC
Join sys.types ST on ST.user_type_id = SC.user_type_id
where SC.object_id = OBJECT_ID(@tablename)
and ST.name like '%int%'

Select @Col2 = SC.name from sys.columns SC
Join sys.types ST on ST.user_type_id = SC.user_type_id
where SC.object_id = OBJECT_ID(@tablename)
and ST.name like '%char%'

--Manipulate the string...
Set @ScriptOutput =
'IF NOT EXISTS (Select 1 from <<TableName>> where <<Col1>> = <<IDVal>>)
	Begin
		Insert <<TableName>> (<<Col1>>, <<Col2>>) Values (<<IDVal>>, <<DescVal>>)
	End
Else
	Begin
		IF NOT EXISTS (Select 1 from <<TableName>> where <<Col1>> = <<IDVal>> and <<Col2>> = <<DescVal>>)
		Update <<TableName>> Set <<Col2>> = <<DescVal>> where <<Col1>> = <<IDVal>>
	End
	'
Select @ScriptOutput = Replace(Replace(Replace(@ScriptOutput, '<<TableName>>', @tablename), '<<Col1>>', @Col1), '<<Col2>>', @Col2)
Insert @FinalScript Select '/* Begin Codes for ' + @tablename + '*/'
While @Counter2 <= @Counter1
Begin
	Select @DescValue = '''' + [Description] + '''' from @ValuesTable where [ID] = @Counter2
	Insert @FinalScript
		Select Replace(Replace(@ScriptOutput, '<<IDVal>>', @Counter2), '<<DescVal>>', @DescValue)
	Select @Counter2 = @Counter2 + 1
End

Insert @FinalScript Select '/* End Codes for ' + @tablename + '*/'
Select * from @FinalScript

Here is the output of this script:

/* Begin Codes for TestAndysAwesomeInsertGenerator*/
IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 1)
	Begin
		Insert TestAndysAwesomeInsertGenerator (TestID, TestDesc) Values (1, 'This is line1')
	End
Else
	Begin
		IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 1 and TestDesc = 'This is line1')
		Update TestAndysAwesomeInsertGenerator Set TestDesc = 'This is line1' where TestID = 1
	End

IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 2)
	Begin
		Insert TestAndysAwesomeInsertGenerator (TestID, TestDesc) Values (2, 'This is line2')
	End
Else
	Begin
		IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 2 and TestDesc = 'This is line2')
		Update TestAndysAwesomeInsertGenerator Set TestDesc = 'This is line2' where TestID = 2
	End

IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 3)
	Begin
		Insert TestAndysAwesomeInsertGenerator (TestID, TestDesc) Values (3, 'This is line3')
	End
Else
	Begin
		IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 3 and TestDesc = 'This is line3')
		Update TestAndysAwesomeInsertGenerator Set TestDesc = 'This is line3' where TestID = 3
	End

IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 4)
	Begin
		Insert TestAndysAwesomeInsertGenerator (TestID, TestDesc) Values (4, 'etc...')
	End
Else
	Begin
		IF NOT EXISTS (Select 1 from TestAndysAwesomeInsertGenerator where TestID = 4 and TestDesc = 'etc...')
		Update TestAndysAwesomeInsertGenerator Set TestDesc = 'etc...' where TestID = 4
	End

/* End Codes for TestAndysAwesomeInsertGenerator*/

This takes care of the data – here are some test cases, run one or both of these scripts, then re-run the output script above and the data will be back to what you initially set it for.

Delete from TestAndysAwesomeInsertGenerator where TestID = 2
--
Update TestAndysAwesomeInsertGenerator set testdesc = 'Row Updated' where testid = 4
--
Select * from TestAndysAwesomeInsertGenerator