[T-SQL] General Insert SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[generalInsert] (@SourceTableName varchar(100), @TargetTableName varchar(100) = '', @WhereClause varchar(8000) = '') with recompile as
Begin
-- Description : Generate Insert queries with data
-- Created By : Farhan Iqbal
-- Dated : 3th March, 2007
-- Example : Exec GeneralInsert 'ACCT_Orders', 'ACCT_Orders', 'Where Order_Id = 1'
Declare @Fields varchar(8000), @Data varchar(max), @SQL nvarchar(max)
set @Fields = ' '
set @Data = ''
if len(ltrim(rtrim(@TargetTableName))) = 0 set @TargetTableName = @SourceTableName
select @Fields = @Fields + ','+Column_Name
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @Data = @Data + ', '+ Case
when Data_Type = 'uniqueidentifier' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varbinary' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'varchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'timestamp' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sysname' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'sql_variant' then +'''''''+isNull(['+Column_Name+'], ''Null'')+'''''''
when Data_Type = 'text' then +'''''''+isNull(replace(convert(varchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'ntext' then +'''''''+isNull(replace(convert(nvarchar(max), ['+Column_Name+']), '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'image' then +''''''''''
when Data_Type = 'xml' then +''''''''''
when Data_Type = 'nvarchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'nchar' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'char' then +'''''''+isNull(replace(['+Column_Name+'], '''''''', ''''''''''''), '''')+'''''''
when Data_Type = 'datetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'smalldatetime' then +'''''''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''''''
when Data_Type = 'bigint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'binary' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'bit' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'decimal' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'float' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'int' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'money' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'numeric' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'real' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'tinyint' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+'''
when Data_Type = 'smallmoney' then +'''+isNull(Convert(Varchar, ['+Column_Name+']), ''Null'')+''' else '' end
from INFORMATION_SCHEMA.columns
where Table_Name = @SourceTableName
order by Ordinal_Position
select @SQL = 'Select ''Insert into '+@TargetTableName+' ('+ Substring (@Fields, 3, len(@Fields))+')
values ('+ substring(rtrim(ltrim(@Data)), 3, len(@Data))+ ')'' from '+@SourceTableName +' '+@WhereClause
execute sp_executesql @SQL
End

[T-SQL] Recursive CTE Contd...

Using recursive CTE normally if level exceeds 100 then it will give statement
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion"

So change the below line


SELECT @PARENT_ID=Min(COALESCE(PARENTPRODUCTGROUP_ID,ID)) FROM RECURSIVECTE option (maxrecursion 32767) ;

[T-SQL] Recursive CTE

Function implemented with normal recursive logic but have limit for 32 levels

CREATE FUNCTION [dbo].[OLD_RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID bigINT
SET @PARENT_ID = NULL
SET @PARENT_ID=
(
SELECT PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP
WHERE ID=@CHILD_ID AND SYSTEM_ID=2
)
IF (ISNULL(@PARENT_ID,0)=0)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)
END

Function implemented with recursive CTE logic - no limits


CREATE FUNCTION [dbo].[RETURN_ROOTID](@CHILD_ID INT)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @PARENT_ID INT;
SET @PARENT_ID = NULL;
WITH RECURSIVECTE AS (
SELECT ID,PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP WHERE ID=@CHILD_ID
UNION ALL
SELECT CO.ID,CO.PARENTPRODUCTGROUP_ID FROM PRODUCTGROUP AS CO
INNER JOIN RECURSIVECTE AS CTE
ON(CO.PARENTPRODUCTGROUP_ID =CTE.ID)
)
SELECT @PARENT_ID=Min(COALESCE(PARENTPRODUCTGROUP_ID,ID)) FROM RECURSIVECTE;
IF (@PARENT_ID=@CHILD_ID)
SET @PARENT_ID=@CHILD_ID
ELSE
SET @PARENT_ID=(SELECT DBO.RETURN_ROOTID(@PARENT_ID))
RETURN(@PARENT_ID)