Script that generates data from an existing table

Guys,


Following script will help generate data script from an existing table.


NOTE: This does not work if you have MONEY data type field in your table.

CREATE Procedure [dbo].[usp_Generate_Inserts] ( @p_tablename varchar(50) )
As
/******************************************************************************
This is a utility stored procedure to generate insert statements.
*******************************************************************************/
Begin
Set NOCOUNT ON
Declare @strSQLMain varchar(8000)
Declare @sSQLInsert varchar(500)
Declare @sSQLFrom varchar(8000)
Declare @sComma char(1)
Declare @sOpenParenthesis char(1)
Declare @sCloseParenthesis char(1)
Declare @singleQuote varchar(10)
Declare @concat varchar(10)
Set @sComma = ','
Set @sOpenParenthesis = '('
Set @sCloseParenthesis = ')'
Set @singleQuote = ''''
Set @concat = '''+'''
Set @sSQLFrom = ''

-- drop if the temp table is not deleted from the previous RUN.
If Exists ( Select 1 From Information_Schema.Tables where Table_Type = 'BASE TABLE' and Table_Name = 'tmpResultsuspGI')
Begin
Drop table tmpResultsuspGI
End


-- Check , if table exists.
If Exists ( Select 1 From Information_Schema.Tables where Table_Type = 'BASE TABLE' and Table_Name = @p_tablename )
begin
-- Get the columns.
declare @name varchar(50),@xtype varchar(50)
declare curColumns cursor
for Select s.name,st.name
from sysColumns s
inner join sysTypes st On s.xtype = st.xtype
where id = Object_ID(@p_tablename) and st.status=0


--based on their data type
select @sSQLInsert = 'INSERT INTO [dbo].[' + @p_tablename+']' +@sOpenParenthesis
open curColumns;
fetch next from curColumns into @name,@xtype
while @@fetch_status = 0
begin
/** Query Format
select cast(countryID as varchar(30) )+ ',''' + CountryCode + '''' + ',''' + countryname + ''''
from Country
**/
select @sSQLInsert = @sSQLInsert + @name + @sComma
if @xtype in ('char','varchar','datetime','smalldatetime','nvarchar','nchar','uniqueidentifier')
begin
select @sSQLFrom = @sSQLFrom + '''''''''' + '+ IsNull(cast(' + @name + ' as varchar(500)),''NULL'') +' + '''''''''' + '+' + ''',''' + '+'
end
else
begin
select @sSQLFrom = @sSQLFrom + 'cast(IsNull(cast(' + @name + ' as varchar(500)),''NULL'') as varchar(500)) ' + '+' + ''',''' + '+'
end
fetch next from curColumns into @name,@xtype
end
close curColumns;
deallocate curColumns;
select @sSQLInsert = substring(@sSQLInsert,1,Len(@sSQLInsert) -1 )
select @sSQLInsert = @sSQLInsert + @sCloseParenthesis
select @sSQLFrom = substring(@sSQLFrom,1,Len(@sSQLFrom) -5 )
select @sSQLFrom = @sSQLFrom + ' as DText'
end
else
begin
Print 'Table does not exists.'
return
end


Set @strSQLMain = 'Select ' + @sSQLFrom + ' into [dbo].tmpResultsuspGI From [' + @p_tablename + ']'
--print @strSQLMain
exec (@strSQLMain)



--Check if there is an identity column or not
If ObjectProperty(Object_ID(@p_tablename),'TableHasIdentity') = 1
Begin
Select 'Set IDENTITY_INSERT [' + @p_tablename + '] ON '
End


Select @sSQLInsert + ' VALUES' + @sOpenParenthesis + Replace(DText,'''NULL''','NULL') + @sCloseParenthesis As [--Statements]
From [dbo].tmpResultsuspGI
If ObjectProperty(Object_ID(@p_tablename),'TableHasIdentity') = 1
Begin
Select 'Set IDENTITY_INSERT ' + @p_tablename + ' OFF '
End


Drop table [dbo].tmpResultsuspGI
End





Now execute it like this:




use northwind

GO
Exec usp_Generate_Inserts 'Order Details'

Go

Exec usp_Generate_Inserts 'Customers'

GO

Do let me know if you have any comments.