Ramesh-Generate Table data as Insert statements

--
-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : To Generate Table Datas as SQL Script
-- Important Note 1: This SP Doesnot Support For image datatype hold columns
-- 2: No cursors,No Looping Used
-------------------------------------------------
--[dbo].[uSP_Sql] 'select * from tAA_Parcel'
-- ==============r===============================
ALTER 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

Group by and Distinct with Ranking Functions

DECLARE @tbl_Groupby TABLE(RevisionID INT ,RevisionNumber VARCHAR(100))

INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (0,'0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R2')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (3,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (3,'R1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (4,'R5')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (5,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (5,'R2')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R7')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (8,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (9,'R12')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (10,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (8,'R4')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (1,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (63,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (64,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (65,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (66,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (67,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (68,'R9')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (50,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (2,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R13')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (6,'R14')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R16')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R3')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (7,'R10')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (51,'R16')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (52,'R15')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (53,'R6')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (54,'R5')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (54,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (55,'R7')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (55,'R11')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (56,'R12')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (57,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (58,'R1')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (59,'R0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (60,'R0')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (61,'R8')
INSERT INTO @tbl_Groupby(RevisionID,RevisionNumber ) VALUES (62,'R12')
--Distinct
SELECT DISTINCT [RevisionID] AS RevisionName
,ROW_NUMBER() OVER ( ORDER BY [RevisionID] ASC) AS Rank
FROM @tbl_Groupby


--
---- Group by
SELECT [RevisionID] AS RevisionName
,ROW_NUMBER() OVER ( ORDER BY [RevisionID] ASC) AS Rank
FROM @tbl_Groupby
GROUP BY [RevisionID]