Generating Table script and insert script of the table in SQL server

Generating table script in sql server

declare @vsSQL varchar(8000) declare @vsTableName varchar(50) select @vsTableName = 'FactInternetSales' –-Table Name select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10) select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' + st.Name + case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end + case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10) from sysobjects so join syscolumns sc on sc.id = so.id join systypes st on st.xusertype = sc.xusertype where so.name = @vsTableName order by sc.ColID select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'

Generating insert script

/****** Object:  StoredProcedure [InsertGenerator]     Script Date: 1/18/2015 10:20:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [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 INTO '+@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('''''+'''''+convert(varchar(200),'+@colName+',121)+''''' +''''',''NULL'')+'',121),''+'   --                         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('''''+'''''+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 exec sp_executesql @query --select @query CLOSE cursCol DEALLOCATE cursCol ---------------

Execute the stored proc.

Exec InsertGenerator FactInternetSales*

*Table name