Generate Insert Statements

IF EXISTS (SELECT 1 from sysobjects where name ='spInsertStatement' and xtype ='P') 
DROP procedure spInsertStatement
GO


/*************************************************************************************************
    Procedure Name :: spInsertStatement
    Creadted by :: Vinay K
    Purpose :: Genterate the insert statement for existing table
*************************************************************************************************/


Create procedure [dbo].[spInsertStatement]
@tbname varchar(200) = '' --- Table name
,@ISPRNExist bit = 0 ---- Print IF Exists statement
,@cehckCol varchar(500) = '' -- column which used in IF Exists statement
as 


set nocount on

----Declare @tbname varchar(88)
----set @tbname ='employee_mas'
IF NOT EXISTS(Select 1 from sysobjects where name = @tbname and xtype = 'U')
BEGIN
    print 'ENTER VALID TABLE NAME'
    RETURN
END

IF NOT EXISTS(Select 1 from syscolumns where name = @cehckCol and id =object_id(@tbname)) and (@cehckCol!='')
BEGIN
    print 'ENTER VALID COLUMN NAME'
    RETURN
END

declare @str varchar(8000) 
Declare @colstat bit
declare @colname varchar(200) 
declare @colstring varchar(8000)
declare @Colvalue varchar(2000)
declare @fetch_status int 
declare @insertstring varchar(8000) 
declare @collen int
declare @inidcount int
Declare @maxinidcount int
 
set @colstring='' 
set @colstat = 0
set @fetch_status=0 
 
---------------------------------------- 
--if exists(select * from sysobjects where id=object_id(N'[dbo].[#tmptable]') and objectproperty(id,'ISTABLE')=1) 
--drop table [dbo].[#tmptable] 

set @str = '
/**********************************************************************************
     Insert statements for Table :: '+ @tbname+'
**********************************************************************************/
'
print (@str)
 
if (select max(colstat) from syscolumns where id = object_id(@tbname)) = 1
set @colstat = 1


if (@colstat = 1)
BEGIN
    print 'SET IDENTITY_INSERT ' +@tbname + ' on'
    print 'GO'
END

--------- chose any one

--declare table #tmptable (colid int identity(1,1),colname varchar(200),colxtype int,coltype varchar(100),colstatus int)
 --or 

create table #tmptable 
( 
colid int identity(1,1), 
colname varchar(200), 
colxtype int, 
coltype varchar(100), 
colstatus int 
) 

 
--set @str='insert into #tmptable (colname,colxtype) select [name],[system_type_id] from sys.columns where [object_id]=object_id(N'''+'[dbo].[' +@tbname + ']'')' 

--This statement for SQL 2000
--set @Str = ' insert into #tmptable (colname,colxtype,coltype) select sys.columns.[name],sys.columns.[system_type_id],sys.types.[name] from sys.columns left join sys.types on sys.columns.[system_type_id] = sys.types.[system_type_id] where [object_id]=object_id(N'''+'[dbo].[' +@tbname + ']'') and sys.types.system_type_id=sys.types.user_type_id and sys.types.system_type_id=sys.types.user_type_id and sys.columns.typestat=1'

-- This statement for SQL 2005
set @Str = ' insert into #tmptable (colname,colxtype,coltype)
select syscolumns.[name],syscolumns.[xtype],systypes.[name] from syscolumns left join systypes 
on syscolumns.[xtype] = systypes.[xtype] where ID=object_id(N'''+'[dbo].[' +@tbname + ']'') 
and systypes.[xtype]=systypes.xusertype 
--and systypes.system_type_id=systypes.user_type_id 
and systypes.[xtype] != 189
order by colid '

-- select sys.columns.[name],sys.columns.[system_type_id],sys.types.[name] from sys.columns left join sys.types on sys.columns.[system_type_id] = sys.types.[system_type_id] where [object_id]=object_id(N'''+'[dbo].[' +@tbname + ']'') and sys.types.system_type_id=sys.types.user_type_id and sys.types.system_type_id=sys.types.user_type_id and sys.types.user_type_id != 189
exec (@str) 
 

--select * from #tmptable
--return
update #tmptable set colstatus=1 where colxtype in (58,61,99,165,167,173,175,231,239) --9 
update #tmptable set colstatus=0 where colxtype in (34,35,36,48,52,56,59,60,62,98,104,106,108,122,127) --15
 

/*-------------------------------------------------------------------------- 
This cursor is used to get the column name from the #tmptable tables. 

------------------------------Start Cursor----------------------------------*/ 
 
Declare @colstatus int 

if (substring(@@version,29,1) = 9)
    Declare @SelectStr varchar(max) -- For sql 2005

--if (substring(@@version,29,1) = 8)
--    Declare @SelectStr varchar(8000) -- For sql 2000
 
declare datatype cursor for select colname, colstatus from #tmptable 
select @SelectStr = null 
open datatype 
fetch NEXT from datatype into @colname,@colstatus 
 
while @@fetch_status = 0 
begin 
if @fetch_status=0 
 set @colstring =@colname 
else 
 set @colstring = @colstring+','+@colname
 
if @colstatus = 1 
 -- set @SelectStr = isnull(@SelectStr,'') + ',' + '''' + @colname + '''' 
 set @SelectStr= case When @SelectStr is null then '' else @SelectStr + '+'',''+' end 
 + '''''''''+ ' + 'cast(ISNULL(' + @colname + ',0) as varchar(250)) ' + '+''''''''' 
else 
 --set @SelectStr = isnull(@SelectStr,'') + ',' + @colname 
 set @SelectStr = case When @SelectStr is null then '' else @SelectStr + '+'',''+' end 
 + 'cast (ISNULL(' + @colname + ',0) as varchar(250)) ' 

--print '@selectstr='+ @selectstr 
     
fetch NEXT from datatype into @colname,@colstatus 
 
set @fetch_status=1 
end 


close datatype 
deallocate datatype
 

 
/*---End Cursor---*/ 
 
 
 
--set @insert_string = 'insert into '+ @tbname + ' ( ' + @colstring + ')' + ' values (' 
--print @insert_string 
--set @SelectStr = 'Select ' + @SelectStr + ' From ' + @tbname 

if exists(select * from sysobjects where id=object_id(N'[dbo].[TMPSELTABLE]') and objectproperty(id,'ISTABLE')=1) 
drop table [dbo].[TMPSELTABLE] 

create table TMPSELTABLE (inid int identity(1,1), SelStr varchar(MAX)) 
 
if exists(select * from sysobjects where id=object_id(N'[dbo].[TM]') and objectproperty(id,'ISTABLE')=1) 
drop table [dbo].[TM] 
 
set @SelectStr = 'Select ' +@SelectStr + ' as Sel into TM From ' + @tbname 
exec (@SelectStr) 
insert into TMPSELTABLE (SelStr) select Sel from TM


if (@cehckCol= '' )
BEGIN
    if (@colstat = 1)
        select @cehckCol = name from syscolumns where id =Object_id(@tbname) and colstat = 1
END
else
BEGIN
    select @cehckCol from syscolumns where id =Object_id(@tbname) and colid =
    (select top 1 min(colid) from syscolumns where id =Object_id(@tbname) group by colid )
END


------------------- cursor that's show the insert statement --------------- 

set @inidcount = 1
select @maxinidcount = max(inid) from TMPSELTABLE


while (@inidcount <= @maxinidcount)
BEGIN

    update TMPSELTABLE set SelStr = replace((replace((replace(substring(selstr,1,len(selstr)-1),'''','''''')),',''''',',''')),''''',',''',')+'''' where inid = @inidcount
    set @str = ''
    select @insertstring = SelStr ,@colvalue = substring(selstr,1,patindex('%,%',selstr) -1 ) from TMPSELTABLE Where inid = @inidcount



    IF (@ISPRNExist    = 1)
    BEGIN
    set @str = 'IF NOT EXISTS (Select 1 from '+ @tbname + ' where '+@cehckCol+' = '+ @Colvalue +')'
    --    set @str ='TESTING'
    END
        
        set @str = @str+'
    insert into '+ @tbname + ' (' + @colstring + ')' + ' 
    values ('+@insertstring + ')

' 
    print (@str)

    Set @inidcount = @inidcount + 1
END
 

 
 
 
drop table #tmptable
drop table TMPSELTABLE
--drop table #colstring
 
--------------------------- OK 
 
 
 
IF (@colstat = 1)
BEGIN
    print 'GO'
    print 'SET IDENTITY_INSERT ' +@tbname + ' off'
    print 'GO'
END
set nocount off


------@tbname varchar(200) = '' --- Table name
------@ISPRNExist bit = o ---- Print IF Exists statement
------@cehckCol varchar(500) = '' -- column which used in IF Exists statement


----Execute spInsertStatement 'Employee'