.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Code to generate script for a table in sql server

Posted By:Maverick       Posted Date: March 16, 2011    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

SP will generate the insert script for the table specified you can also specify the where criteria to generate script for required records.
 

/****** Object:  StoredProcedure [dbo].[InsertGenerator]    Script Date: 02/16/2011 12:18:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--  exec insertgenerator 'YOUR Table Nale'
 
ALTER PROC [dbo].[InsertGenerator]  
(@tableName varchar(100)) as  
 
--Declare a cursor to retrieve column specific information for the specified table  
DECLARE cursCol CURSOR FAST_FORWARD FOR   
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName  
OPEN cursCol  
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement  
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement  
DECLARE @dataType nvarchar(1000) --data types returned for respective columns  
SET @string='INSERT '+@tableName+'('  
SET @stringData=''  
 
DECLARE @colName nvarchar(50)  
 
FETCH NEXT FROM cursCol INTO @colName,@dataType  
 
IF @@fetch_status<>0  
    begin  
    print 'Table '+@tableName+' not found, processing skipped.'  
    close curscol  
    deallocate curscol  
    return  
  END  
    
  WHILE @@FETCH_STATUS=0  
  BEGIN  
  IF @dataType in ('varchar','char','nchar','nvarchar')  
  BEGIN  
      --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'  
      SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'  
  END  
  ELSE  
  if @dataType in ('text','ntext') --if the datatype is text or something else   
  BEGIN  
      SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'  
  END  
  ELSE  
  IF @dataType = 'money' --because money doesn't get converted from varchar implicitly  
  BEGIN  
      SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'  
  END  
  ELSE   
  IF @dataType='datetime'  
  BEGIN  
      --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'  
      --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations  
      --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'  
      SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'  
    --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations  
  END  
  ELSE   
  IF @dataType='image'   
  BEGIN  
      SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'  
  END  
  ELSE --presuming the data type is int,bit,numeric,decimal   
  BEGIN  
      --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'  
      --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'  
      SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'  
  END  
    
  SET @string=@string+@colName+','  
    
  FETCH NEXT FROM cursCol INTO @colName,@dataType  
  END  
  DECLARE @Query nvarchar(4000)  
    
  SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
  print @query
  exec sp_executesql @query  
  --select @query  
    
  CLOSE cursCol  
  DEALLOCATE cursCol  
    
   

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend