[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)

[T-SQL] Find Statistics Update Date – Update Statistics

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('LEARNER')

[T-SQL] Get Date of All Weekdays or Weekends of the Year

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

[T-SQL] Find Location of Data File

SELECT SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 1) DataFileLocationFROM master.sys.master_filesWHERE database_id = 1 AND FILE_ID = 1

[T-SQL] Generate Data script of any specific table

http://vyaskn.tripod.com/code/generate_inserts_2005.txt