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)

As

/*

Generate Insert Queries for a given table

Created by: Julian Vargas

Url: http://gr8code.blogspot.com

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

End

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

Exec(@Insert)

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.