Tuesday, 19 October 2010

Generate Insert Queries for a given table

Oftentimes I face the challenge of migrating data from one SQL Server database to another of the same type. I'm not going to discuss the reasons why I chose to do it this way, I just want to share this utility with the community because it has been very useful for me. The solution is to create an stored procedure that generates insert queries for every single record in a given table, like this:

Exec dbo.GenerateInsertQueriesFor 'Student'

By running the procedure I will get in the results pane, an insert query with the values of each tuple in the table Student. Then I can copy the results and paste it and run it in other database. The procedure uses the information_schema to introspect the table and know what columns it has. Each column name is concatenated to the insert query and all values are treated as string. Here is the source code of the stored procedure:

Create Procedure dbo.GenerateInsertQueriesFor

@TableName Varchar(max)



Generate Insert Queries for a given table

Created by: Julian Vargas


On: October 19 2010


Declare @ColumnName varchar(max)

Declare @DataType Varchar(max)

Declare @Insert Varchar(max)


--Initial structure of the insert query

Set @Insert = 'Select ''Insert Into '

            + @TableName

            + ' Values('


--A cursor to navigate through the list of columns in the table

--specified by @TableName

Declare Columnas Cursor For

    --Uses the information schema for listing the columns

    Select Column_Name, Data_Type

    From Information_Schema.Columns

    Where Table_Name =@TableName


Open Columnas

Fetch Next From Columnas Into @ColumnName, @DataType


The final query will look like this:


    Select 'Insert Into Student Values(' + ''''

    + Cast(IsNull(StudentId,0) As Varchar(max))

    + '''' + ',' + '''' + Cast(IsNull(Name,'') As Varchar(max))

    + '''' + ')'

    From Student


after running the query, the result will look like this:


    Insert Into Student Values('1','Jhon')


Notice that all columns are casted to varchar for allowing concatenation

and also are inside quotes to treat them as string and let SQL Server

to convert them to their corresponding values.


To determine if the datatype is any numerical type, the

funcion CharIndex is used like this:

CharIndex(' int '      , ' bigint int smallint ...') is equal to 8

CharIndex(' smallint ' , ' bigint int smallint ...') is equal to 12

CharIndex(' varchar ' , ' bigint int smallint ...') is equal to 0



While @@Fetch_status = 0 Begin

    Set @Insert = @Insert

    -- 1quote 4quotes

    +    ''' + '''''''' + Cast(IsNull('+@ColumnName+','+

    Case CharIndex(' '+@DataType+ ' ', ' bigint int '

    +'smallint tinyint bit decimal numeric money '

    +'smallmoney float real ')

    --          2quotes

    When 0 Then '''''' Else '0' End

    --                     4quotes

    +') As Varchar(max)) + '''''''' + '','


Fetch Next From Columnas Into @ColumnName, @DataType


Close Columnas

Deallocate Columnas


--Remove the last comma

Set @Insert = Substring(@Insert, 1, Len(@Insert)-1)


--Closes the sentence

Set @Insert = (@Insert + ')'' From ' + @TableName)


--Runs the query


One thing to keep in mind is that you have to enable identity insert before running the final scripts.
I hope you find the procedure useful as I do.

Monday, 18 October 2010

My lovely table

This project is a
plugin for displaying tabular data into html tables, this was inspired
by some personal needs but it is aimed at satisfying all the patterns described
in the post
Ultimate guide to table UI patterns
by Janko Jovanovic,
Ultimate guide to table UI patterns
by Theresa Neil,
15 tips for designing terrific tables
by Joshua Johnson and
The big table issue
by Jin. The plugin is going to be delivered
progresively as the following requirements are satisfied.

  • Alternating row styling
  • Full row selection
  • Table sections: Grouping relateddata
  • Sorting
  • Filtering
  • Pagination
  • Continious scrolling: As a constrast to pagination, shows new rows when scrolling
  • Fiex table header
  • Headerless table
  • Expandable rows
  • Row actions
  • Inline actions: at the beginning or the end of the row.
  • Hover actions: display a menu somewhere in the data when certain event occurs.
  • Actions on multiple rows
  • Inline editing
Another important requirement is to allow the footer to contain controls for adding
new rows to the table