Tuesday, June 10, 2014

Script to automatically create insert statements for table data in SQL Server

CREATE procedure  [dbo].[INS]                             
(                                                         
   @Query  Varchar(MAX)                                                         
)                             
AS                            
   Set nocount ON                 
DEclare @WithStrINdex as INT                           
DEclare @WhereStrINdex as INT                           
DEclare @INDExtouse as INT                            
Declare @SchemaAndTAble VArchar(270)                           
Declare @Schema_name  varchar(30)                           
Declare @Table_name  varchar(240)                           
declare @Condition  Varchar(MAX)                             
SET @WithStrINdex=0                           
SELECT @WithStrINdex=CHARINDEX('With',@Query )                           
, @WhereStrINdex=CHARINDEX('WHERE', @Query)                           
IF(@WithStrINdex!=0)                           
Select @INDExtouse=@WithStrINdex                           
ELSE                           
Select @INDExtouse=@WhereStrINdex                           
Select @SchemaAndTAble=Left (@Query,@INDExtouse-1)                                                     
select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))                           
Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)                           
,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )                           
,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                           
Declare   @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )                             
Declare @CONDITIONS as varchar(MAX)                             
Declare @Total_Rows as SmallINT                             
Declare @Counter as SmallINT             
declare @ComaCol as varchar(max)           
select @ComaCol=''                  
Set @Counter=1                             
set @CONDITIONS=''                             
INsert INTO @COLUMNS                             
Select  Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name                             
And table_name=@Table_name        
and Column_Name not in ('SyncDestination','PendingSyncDestination' ,'SkuID','SaleCreditedto')                  
select @Total_Rows= Count(1) FRom  @COLUMNS                             
             Select @Table_name= '['+@Table_name+']'                     
             Select @Schema_name='['+@Schema_name+']'                     
While (@Counter<=@Total_Rows )                             
begin                              
--PRINT @Counter                             
    select @ComaCol= @ComaCol+'['+Column_Name+'],'           
    FROM @COLUMNS                             
Where [Row_number]=@Counter                         
select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+                             
 Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  )                             
  +'''''''' end+'+''','''                             
FROM @COLUMNS                             
Where [Row_number]=@Counter                             
SET @Counter=@Counter+1                              
End                             
select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)                             
select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)             
select @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                           
select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS                             
select @CONDITIONS=@CONDITIONS+'+'+ ''')'''                             
Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition                             
print(@CONDITIONS)                             
Exec(@CONDITIONS) 
Exec [dbo].[INS]  'Person.PersonPhone where 1=1'