Monday, September 14, 2009

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.

3 comments:

Amulya Dutta said...

"clustered index can be created only on table that has unique non null values"

This statement is wrong..Clustered index can be created on non-unque null values as well.

Try this code snippet.


------------------------------

create table test
(
id int null
)

insert into test(id) values (null)
insert into test(id) values (1)
insert into test(id) values (null)
insert into test(id) values (2)
insert into test(id) values (2)
insert into test(id) values (1)
GO

CREATE CLUSTERED INDEX TX_TEST ON TEST(ID)
GO
------------------------------

Sudhir Chawla said...

Thanks for the correction.

This is linked to "Basic SQL Questions" post.

Sudhir Chawla said...

Begin
Set NOCOUNT ON
Declare @strSQLMain varchar(8000)
Declare @sSQLInsert varchar(5000)
Declare @sSQLFrom varchar(8000)
Declare @sComma char(1)
Declare @sOpenParenthesis char(1)
Declare @sCloseParenthesis char(1)
Declare @singleQuote varchar(10)
Declare @concat varchar(10)
Declare @p_tablename varchar(100)

Set @sComma = ','
Set @sOpenParenthesis = '('
Set @sCloseParenthesis = ')'
Set @singleQuote = ''''
Set @concat = '''+'''
Set @sSQLFrom = ''

Set @p_tablename = 'prf_invst_trans_subtyp_ref'

-- 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
Create table #tmpResultsuspGI
(DText varchar(8000))


-- 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.xusertype
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

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 = 'Insert into #tmpResultsuspGI Select ' + @sSQLFrom + ' 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 #tmpResultsuspGI
If ObjectProperty(Object_ID(@p_tablename),'TableHasIdentity') = 1
Begin
Select 'Set IDENTITY_INSERT ' + @p_tablename + ' OFF '
End


Drop table #tmpResultsuspGI
End