The stored procedure that follows is able to generate insert scripts for data in the table. When you execute this, it expects the table name as argument.
=======================================================
-- Description : To Generate Table Datas as SQL Script
-- Important Note : This SP Doesnot Support For image datatype hold columns
-- No cursors,No Looping Used
-------------------------------------------------
--[dbo].[uSP_Sql] 'tAA_Parcel'
-- ==============r===============================
CREATE PROC [dbo].[uSP_Sql]
@vchSqlQuery VARCHAR(MAX)
--WITH ENCRYPTION
AS
DECLARE @InsertStmt VARCHAR(128) -- hold the Insert stmt
, @ValuesStmt VARCHAR(128) -- hold the values stmt
, @vchTableName VARCHAR(128)
, @vchNewTblName VARCHAR(128)
, @InsertText VARCHAR(MAX) -- hold the Insert stmt text
, @ValuesText VARCHAR(MAX) -- hold the Values stmt text
, @SQLStmt NVARCHAR(MAX) -- hold the whole SQL stmt
, @vchTmpTblQuery VARCHAR(MAX)
, @iFromPos INT
, @iWherePos INT
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SET @vchSqlQuery = LTRIM(RTRIM(@vchSqlQuery))
SET @iFromPos = CHARINDEX(' FROM ',@vchSqlQuery,0)
IF (@iFromPos = 0 )
BEGIN
SET @vchTableName = @vchSqlQuery
SET @vchSqlQuery = 'SELECT * FROM '+LTRIM(RTRIM(@vchSqlQuery))
END
ELSE IF (@iFromPos > 0 )
BEGIN
SET @iWherePos = ISNULL(ISNULL(ISNULL(NULLIF(CHARINDEX('WHERE ',@vchSqlQuery,0),0),NULLIF(CHARINDEX('GROUP ',@vchSqlQuery,0),0)),NULLIF(CHARINDEX('ORDER ',@vchSqlQuery,0),0)),LEN(@vchSqlQuery)+1)
SET @vchTableName = SUBSTRING(@vchSqlQuery,@iFromPos +6,@iWherePos - @iFromPos -4)
END
SET @iWherePos = ISNULL(ISNULL(ISNULL(NULLIF(CHARINDEX('WHERE ',@vchSqlQuery,0),0),NULLIF(CHARINDEX('GROUP ',@vchSqlQuery,0),0)),NULLIF(CHARINDEX('ORDER ',@vchSqlQuery,0),0)),LEN(@vchSqlQuery)+1)
SET @vchNewTblName = 'TempTbl'+LTRIM(RTRIM(STR(@@SPID)))
SET @vchTmpTblQuery = REPLACE (@vchSqlQuery, 'FROM',' INTO '+@vchNewTblName+' FROM ')
IF EXISTS( SELECT 'X' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @vchNewTblName)
BEGIN
EXEC( 'DROP TABLE '+@vchNewTblName)
END
EXEC (@vchTmpTblQuery)
SET @InsertText = 'INSERT INTO ' + @vchTableName +'('
SET @ValuesText = ' VALUES ('''+'+' ;
WITH RankTable(RowNumber,ColumnName,DataType) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION)AS RowNumber
,COLUMN_NAME
,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE NOT IN ('image')
AND TABLE_NAME = @vchNewTblName
)
SELECT @InsertText = @InsertText + CASE RowNumber WHEN 1 THEN ColumnName
ELSE ','+ ColumnName
END
, @ValuesText = @ValuesText + CASE RowNumber WHEN 1 THEN ''
ELSE '+'',''+'
END
+ CASE WHEN DataType IN ( 'TINYINT' ,'SMALLINT','INT','REAL','MONEY','FLOAT','BIT','DECIMAL','NUMERIC','SMALLMONEY','BIGINT')
THEN 'COALESCE(CONVERT(VARCHAR(128),'+ ColumnName+'),''NULL'')'
--THEN ' CONVERT(VARCHAR(128),'+ ColumnName+')'
WHEN DataType IN ('XML','TEXT','NTEXT','SQL_VARIANT' ,'BINARY','VARBINARY','TIMESTAMP')
THEN ''''''''''+'+COALESCE(REPLACE(RTRIM(LTRIM('+ColumnName+')),'''''''',''''''''''''),''NULL'') +'+''''''''''
WHEN DataType IN ('Datetime','smalldatetime') THEN
''''''''''+'+CONVERT(VARCHAR(25),COALESCE('+ColumnName+',''1/1/1900''),101) +'+''''''''''
ELSE ''''''''''+'+COALESCE(REPLACE(RTRIM(LTRIM('+ColumnName+')),'''''''',''''''''''''),''NULL'') +'+''''''''''
END
FROM RankTable
ORDER BY RowNumber ASC
SET @InsertText = @InsertText + ' )'
SET @SQLStmt = 'DECLARE @tblScript TABLE ( AscID INT IDENTITY(1,1) ,vchSQL VARCHAR(MAX)) '
SELECT @InsertText = 'INSERT INTO @tblScript SELECT '+''''+ @InsertText + @ValuesText +'+'')''' +' FROM '+@vchNewTblName
IF ( (SELECT ISNULL( (SELECT 1 FROM sys.syscolumns WHERE object_name(id) = @vchTableName AND colstat = 1) ,0)) = 1 )
BEGIN
SELECT @InsertText = 'INSERT INTO @tblScript SELECT '+''''+ 'SET IDENTITY_INSERT ' + @vchTableName + ' ON ' +''''+char(10)+char(10)
+@InsertText +char(10)+char(10)
+'INSERT INTO @tblScript SELECT '+''''+ 'SET IDENTITY_INSERT ' + @vchTableName + ' OFF ' +''''
END
ELSE
BEGIN
SELECT @InsertText = @InsertText
END
SELECT @SQLStmt = @SQLStmt + @InsertText + +char(10)+char(10) +' SELECT REPLACE(REPLACE(vchSQL,''01/01/1900'',''NULL''),''''''NULL'''''',''NULL'') FROM @tblScript ORDER BY AscID ASC'
EXEC( @SQLStmt)
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
SELECT ERROR_MESSAGE()
-- RETURN -999
END CATCH
SET NOCOUNT OFF;
END
GO
