LINQ Extended method for Update Data

Extended Method

public delegate void Func(TArg0 element);

/// 
/// Executes an Update statement block on all elements in an IEnumerable sequence.
/// 
/// The source element type.
/// The source sequence.
/// The update statement to execute for each element.
/// The numer of records affected.
public static int Update(this IEnumerable source, Func update)
{
    if (source == null) throw new ArgumentNullException("source");
    if (update == null) throw new ArgumentNullException("update");
    if (typeof(TSource).IsValueType)
        throw new NotSupportedException("value type elements are not supported by update.");

    int count = 0;
    foreach (TSource element in source)
    {
        update(element);
        count++;
    }
    return count;
}

Example :

int intUpdate = lstItem
                .Where(a => a.ItemGroupId.Equals(lngItemGroupId))
                .Update(b =>
                            {
                                b.GroupName = strGroupName;
                                b.GroupLocation = strLocation;
                            }
                );

Truncate Log

--BACKUP LOG  WITH TRUNCATE_ONLY
--Go


--============= For sql 2008 =====================
USE
GO
SELECT file_id, name FROM sys.database_files
GO
DBCC SHRINKFILE (2, TRUNCATEONLY); 

GET Database diffrence

----Declaration
BEGIN 
 DECLARE @Status_TableNotFound  INT = 1
 DECLARE @Status_FieldNotFound  INT = 2
 DECLARE @Status_FieldTypeChnage  INT = 3
 DECLARE @Status_FieldLengthChange INT = 4
ENd

--Get Table / Filed List
BEGIN
 SELECT 
  --- db1
  db1Objects.id AS db1Id
  , db1Objects.name AS db1TableName
  , db1Column.name AS db1FieldName 
  , db1Column.xType as db1FieldType 
  , UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
  , db1Column.length as db1FieldLength
  , db1Column.ColStat & 1 AS db1IsIdentity

  --- db2
  , ISNULL( db2Objects.id, 0) AS db2Id
  , ISNULL( db2Objects.name, '') AS db2TableName
  , ISNULL( db2Column.name, '') AS db2FieldName 
  , ISNULL( db2Column.xType, 0 ) as db2FieldType 
  , UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
  , ISNULL( db2Column.length, 0 ) as db2FieldLength
  , ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
  , 0 AS Status
 INTO #FieldCheck
 FROM <DBName1>.dbo.SysObjects AS db1Objects 
  INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
   AND db1Objects.xtype = 'U'
   AND db1Objects.category = 0
  
  LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
   AND db2Objects.xtype = 'U'
   AND db2Objects.category = 0
  LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
   AND db2Column.name = db1Column.name
END

--- Check for Status
BEGIN
 --- TableNotFound
 UPDATE #FieldCheck
 SET Status = @Status_TableNotFound
 WHERE db1TableName != db2TableName
 
 --- FieldNotFound
 UPDATE #FieldCheck
 SET Status = @Status_FieldNotFound
 WHERE db1FieldName != db2FieldName
  AND Status = 0
 
 --- FieldTypeChnage
 UPDATE #FieldCheck
 SET Status = @Status_FieldTypeChnage
 WHERE db1FieldName = db2FieldName
  AND db1FieldTypeName != db2FieldTypeName
  AND Status = 0
 
 --- FieldLengthChange
 UPDATE #FieldCheck
 SET Status = @Status_FieldLengthChange
 WHERE db1FieldLength != db2FieldLength
  AND Status = 0
END


--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound

--- FieldNotFound
SELECT DISTINCT 
 db1TableName AS TableName
 , db1FieldName FieldNotFound
 , 'ALTER TABLE ' + db1TableName + 
  ' ADD ' + db1FieldName + 
  ' ' + db1FieldTypeName + 
  ' ' + ( 
    CASE 
     WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
      THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
     ELSE ''
    END
        ) +
  '; UPDATE ' + db1TableName +
  ' SET  ' + db1FieldName + ' = ' +
  (
   CASE
    WHEN db1FieldTypeName LIKE '%CHAR%' 
      OR db1FieldTypeName LIKE '%TEXT%' 
      OR db1FieldTypeName LIKE '%XML%' 
     THEN ''''''
    WHEN db1FieldTypeName LIKE 'INT' 
      OR db1FieldTypeName LIKE 'SMALLINT'
      OR db1FieldTypeName LIKE 'BIGINT'
      OR db1FieldTypeName LIKE '%BINARY%'
      OR db1FieldTypeName LIKE 'float'
      OR db1FieldTypeName = 'BIT'
     THEN '0'
    ELSE 'object'
   END
  )
  ' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound

--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage


--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange

DROP TABLE #FieldCheck

Find and Replace text in Datatable

SET NOCOUNT ON
DECLARE @FindName     VARCHAR(MAX) = 'AAA' --- <- Find Value
DECLARE @ReplaceName  VARCHAR(MAX) = 'BBB' --- <- Replace Value

DECLARE @Table TABLE
(
 ID INT IDENTITY(1,1)
 , TableName  VARCHAR(100)
 , ColumnName VARCHAR(100)
)
INSERT INTO @Table
 SELECT
  tableSchema.TABLE_SCHEMA + '.' + SysObjects.name
  , SysColumns.Name
 FROM sysobjects 
  INNER JOIN SysColumns ON SysColumns.id = sysobjects .id
   AND SysColumns.xtype IN ( 167, 175, 239, 231, 35, 99 )
  INNER JOIN information_schema.tables AS tableSchema ON tableSchema.TABLE_NAME = SysObjects.name
 WHERE sysobjects.xtype='U'
 ORDER BY sysobjects.Name, SysColumns.Name
 
DECLARE @NoOfRecord INT = 0 
DECLARE @CurrentRecord INT = 1
DECLARE @TableName VARCHAR(100) = ''
DECLARE @ColumnName VARCHAR(100) = ''
DECLARE @SQLCommand VARCHAR(MAX) = ''

SELECT @NoOfRecord = MAX(ID)
FROM @Table

WHILE (@CurrentRecord <= @NoOfRecord )
 BEGIN
  SELECT
   @TableName = TableName
   , @ColumnName = ColumnName
  FROM @Table
  WHERE ID = @CurrentRecord
  
  SET @SQLCommand = 'IF EXISTS(SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindName + '%'')' +
   ' BEGIN ' +
   ' PRINT ''UPDATE ' + @TableName +
   ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''''' + @FindName + ''''', ''''' + @ReplaceName + ''''') ''' +
   ' END '
   
  -- PRINT @SQLCommand
  EXECUTE(@SQLCommand)
  SET @CurrentRecord = @CurrentRecord + 1
 END

Second highest Salary

DECLARE @Employees TABLE(
    EmployeeID INT IDENTITY,
    EmployeeName VARCHAR(15),
    Department VARCHAR(15),
    Salary NUMERIC(16,2)
)
 
INSERT INTO @Employees VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees VALUES('jj','Finance', 25000)
INSERT INTO @Employees VALUES('D Adams','Finance', 15000)
 
INSERT INTO @Employees VALUES('M Williams','IT', 80000)
INSERT INTO @Employees VALUES('D Jones','IT', 40000)
INSERT INTO @Employees VALUES('J Miller','IT', 50000)
INSERT INTO @Employees VALUES('L Lewis','IT', 50000)
 
INSERT INTO @Employees VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees VALUES('T Clerk','Back-Office', 10000)

SELECT * 
FROM
(
 SELECT *, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmpRank
 FROM @Employees
) AS Employees
WHERE EmpRank = 2

Get field list from string

DECLARE @FileFormat VARCHAR(50)
 SET @FileFormat = '1#34#6#8#01#34#6#8#01#34#6#8#01#34#6#8#0'
 
 ;WITH MyTable AS 
 (
  SELECT @FileFormat AS FileFormat
   , CHARINDEX('#', @FileFormat) + 1 AS FirstIndex
   , (
    CHARINDEX('#', SUBSTRING(@FileFormat, CHARINDEX('#', @FileFormat ) + 1, LEN( @FileFormat ) ) ) - 1
    ) AS SecondIndex
  
  UNION ALL
  
  SELECT SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) AS FileFormat
   , CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1 AS FirstIndex
   , (
    CHARINDEX('#', SUBSTRING(SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ), CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1, LEN( FileFormat ) ) ) - 1
    ) AS SecondIndex
  FROM MyTable
  WHERE LEN( FileFormat ) > 0
   AND SecondIndex > 0
 )
 
 SELECT 
  (
   CASE 
    WHEN FirstIndex - 2 > 0 THEN SUBSTRING(FileFormat, 1, FirstIndex - 2)
    ELSE SUBSTRING(FileFormat, 1, LEN( FirstIndex ) )
   END
  ) AS PreviousField
  ,(
   CASE 
    WHEN SecondIndex > 0 THEN SUBSTRING(FileFormat, FirstIndex, SecondIndex)
    ELSE ''
   END
  ) AS FieldName
 FROM MyTable
 OPTION (MAXRECURSION 0)

Get Datatabase LOG Size

SELECT
     (size * 8)/1024.0 AS size_in_mb,
     CASE
        WHEN CAST( max_size AS BIGINT )= -1 THEN 9999999   -- Unlimited growth, so handle this how you want
        ELSE (CAST( max_size AS BIGINT ) * 8)/1024.0
     END AS max_size_in_mb
FROM
     sys.database_files
WHERE
     data_space_id = 0   -- Log file

Insert query of datatable

DECLARE @PassTableName as varchar(1000)

SET @PassTableName = 'tbl_ReturnsEFileFieldMapping' --- 'Type Table Name'

BEGIN

 DECLARE @SQL_Insert AS Varchar(8000)
 DECLARE @SQL_Values AS Varchar(8000)

 DECLARE @FieldName AS Varchar(200)
 DECLARE @FieldType as int
 DECLARE @FieldTypeName as varchar(100)
 DECLARE @FieldLength as int
 DECLARE @FieldIdentity as int

 SET @SQL_Insert = 'SELECT ''INSERT INTO ' + @PassTableName + ' ( '
 SET @SQL_Values = ' VALUES ( '

 DECLARE Fields_Cursor CURSOR FOR 
  SELECT Name AS FieldName, 
   xType as FieldType, 
   Type_Name(xType) AS FieldTypeName, 
   Length as FieldLength, 
   ColStat & 1 AS IsIdentity
  FROM SysColumns 
  WHERE ID IN ( SELECT ID FROM SysObjects WHERE Name = REPLACE( REPLACE( REPLACE( @PassTableName, 'dbo.', ''), 'Report.', ''), 'EQISAdmin.', '') )

 OPEN Fields_Cursor
 FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

 WHILE @@FETCH_STATUS = 0
 BEGIN

--  IF @FieldIdentity = 0 -- Identity is No
--  BEGIN

   SET @SQL_Insert = @SQL_Insert + @FieldName + ', '

   IF @FieldType = 48 OR @FieldType = 56 OR @FieldType = 127 OR @FieldType = 59 OR @FieldType = 52  -- Small Int OR Integer OR Big Integer OR Real OR SmallInt
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(20), ISNULL(' + @FieldName + ', '''' ) ) + '', '
    END
   ELSE IF @FieldType = 106 OR @FieldType = 108 -- Decimal OR Numeric
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), ISNULL(' + @FieldName + ', '''') ) + '', '
    END
   ELSE IF @FieldType = 60 OR @FieldType = 122 -- Money OR Small Money
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,4), ISNULL(' + @FieldName + ', 0) )) + '', '
    END
   ELSE IF @FieldType = 62 -- Float
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,10), ISNULL(' + @FieldName + ',0) )) + '', '
    END
   ELSE IF @FieldType = 104  -- Bit
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(1), ISNULL(' + @FieldName + ','''') ) + '', '
    END
   ELSE IF @FieldType = 61 OR @FieldType = 58  -- Datetime OR SmallDatetime
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Convert( varchar(20), ISNULL(' + @FieldName + ','''') ) + '''''', '
    END
   ELSE IF @FieldType = 167 OR @FieldType = 175 OR @FieldType = 239 OR @FieldType = 231 -- Varchar OR Char OR nChar OR nVarchar
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( ISNULL(' + @FieldName + ', ''''), '''''''', '''''''''''' ) + '''''', '
    END
   ELSE IF @FieldType = 35 OR @FieldType = 99 -- Text OR nText
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', ''''''''''''  ) + '''''', '
    END
   ELSE IF @FieldType = 241 -- XML
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', ''''''''''''  ) + '''''', '
    END

--  END


    FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

 END

 CLOSE Fields_Cursor
 DEALLOCATE Fields_Cursor

 SET @SQL_Insert = LTRIM( RTRIM( @SQL_Insert ) )
 IF LEN( @SQL_Insert ) > 1
  BEGIN
   SET @SQL_Insert = SUBSTRING( @SQL_Insert, 1, LEN( @SQL_Insert ) -1 )
  END

 SET @SQL_Values = LTRIM( RTRIM( @SQL_Values ) )
 IF LEN( @SQL_Values ) > 1
  BEGIN
   SET @SQL_Values = SUBSTRING(@SQL_Values, 1, LEN( @SQL_Values ) -1 )
  END
 
 --PRINT ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )
 EXEC  ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )

END
GO

Hierarchical Tree Structures in Database Tables

DECLARE @emp TABLE ( 
EmployeeID INT, 
FirstName VARCHAR(15), 
LastName VARCHAR(15), 
ReportsTo INT 
) 
 

DECLARE @ord TABLE ( 
OrderID INT, 
EmployeeID INT 
) 
 

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo) 
SELECT 2,'Andrew','Fuller',NULL
UNION ALL SELECT 1,'Nancy','Davolio',2 
UNION ALL SELECT 3,'Janet','Leverling',2 
UNION ALL SELECT 4,'Margaret','Peacock',2 
UNION ALL SELECT 5,'Steven','Buchanan',2 
UNION ALL SELECT 8,'Laura','Callahan',2 
UNION ALL SELECT 6,'Michael','Suyama',5 
UNION ALL SELECT 7,'Robert','King',5 
UNION ALL SELECT 9,'Anne','Dodsworth',5  
--UNION ALL SELECT 10,'A','A',1  
--UNION ALL SELECT 11,'B','B',1  
--UNION ALL SELECT 12,'C','C',1   
--UNION ALL SELECT 13,'D','D',10  

--SELECT * FROM @emp 
 

INSERT INTO @ord (OrderID, EmployeeID)  
SELECT 10258,1 UNION ALL 
SELECT 10270,1 UNION ALL 
SELECT 10275,1 UNION ALL 
SELECT 10265,2 UNION ALL 
SELECT 10277,2 UNION ALL 
SELECT 10251,3 UNION ALL 
SELECT 10253,3 UNION ALL 
SELECT 10256,3 UNION ALL 
SELECT 10250,4 UNION ALL 
SELECT 10252,4 UNION ALL 
SELECT 10248,5 UNION ALL 
SELECT 10254,5 UNION ALL 
SELECT 10249,6 UNION ALL 
SELECT 10289,7 UNION ALL 
SELECT 10303,7 UNION ALL 
SELECT 10308,7 UNION ALL 
SELECT 10262,8 UNION ALL 
SELECT 10268,8 UNION ALL 
SELECT 10276,8 UNION ALL 
SELECT 10278,8 UNION ALL 
SELECT 10255,9 UNION ALL 
SELECT 10263,9  
 

;WITH cteNodes AS
(
 --initialization
 SELECT EmployeeID, ISNULL(ReportsTo, 0 ) AS ReportsTo, 0 AS EmpLevel
 FROM @emp

 UNION ALL

 ----recursive execution
 SELECT P.EmployeeID, ISNULL( N.ReportsTo, 0 ), P.EmpLevel + 1
 FROM cteNodes AS P
  INNER JOIN @emp AS N ON N.EmployeeID = P.ReportsTo
)
, cteEmployeePath AS
(
 SELECT DISTINCT Emp.EmployeeID
  , ISNULL( Emp.ReportsTo, 0 ) AS ReportsTo
  ,(
   SELECT '$' + CAST(ReportsTo AS VARCHAR(20) ) + '$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) 
   + '$' + CAST( Emp.EmployeeID AS VARCHAR(10) ) + '$' AS EmployeePath
   ,(
   SELECT '$0$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) AS EmpLevel
 FROM @emp AS Emp
)

SELECT
  (
  REPLICATE(' ', 3*( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) ) 
  + Emp.LastName
  + ' ' 
  + Emp.FirstName 
  ) AS EmployeeName
  , ( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) AS Level
  , SUM( CASE WHEN Ord.EmployeeID = Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_self
  , SUM( CASE WHEN Ord.EmployeeID != Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_sub
  , COUNT( Ord.OrderId ) AS total 
FROM cteEmployeePath 
 INNER JOIN @emp AS Emp ON Emp.EmployeeID = cteEmployeePath.EmployeeID
 INNER JOIN cteEmployeePath AS UnderEmployee ON UnderEmployee.EmployeePath Like cteEmployeePath.EmployeePath + '%'
 INNER JOIN @ord AS Ord ON Ord.EmployeeID = UnderEmployee.EmployeeID
GROUP BY Emp.EmployeeId, cteEmployeePath.EmpLevel, Emp.FirstName, Emp.LastName, cteEmployeePath.EmployeePath
ORDER BY cteEmployeePath.EmployeePath

Backup & Restore Database

---- Backup Database :
BEGIN
    backup database  to disk = 'c:\.bak'
END

---- Restore Database :
BEGIN
  RESTORE DATABASE Salary 
  FROM  DISK = N'C:\DBBackUp 2009-12-23.bak' 
  WITH  FILE = 1,  
  MOVE N'Salary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf',  
 MOVE N'Salary_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL  \Data\DB_Log.ldf',  
   NOUNLOAD,  STATS = 10
END

Fibonacci Sequence

WITH FibonacciSequence AS
(

    SELECT 
  1 AS SrNo
  , ( CAST( 0 AS FLOAT ) ) AS c1
  , ( CAST( 1 AS FLOAT ) ) AS c2

    UNION ALL

    SELECT 
  SrNo + 1 AS SrNo
  , ( CAST( c2 AS FLOAT ) ) AS c1
  , ( CAST( ( c1 + c2 ) AS FLOAT ) ) AS c2
    FROM FibonacciSequence
    WHERE SrNo < 100

)

SELECT SrNo, c1 
FROM FibonacciSequence
OPTION (MAXRECURSION 0);

Digital Scanner Result

SET NOCOUNT ON
DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))
 
INSERT INTO @t
SELECT  1,--> 000 007 059
' _  _  _  _  _  _  _  _  _
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                            
'
UNION
SELECT  2, --> 490 067 715
'    _  _  _  _  _  _     _
|_||_|| || ||_   |  |  ||_
  | _||_||_||_|  |  |  | _|
                            
'
UNION
SELECT  3, --> 680 X68 279
' _  _  _     _  _  _  _  _
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                            
'
UNION
SELECT  4, --> 490 867 716
'    _  _  _  _  _  _     _
|_||_|| ||_||_   |  |  ||_
  | _||_||_||_|  |  |  ||_|
                            
'
UNION
SELECT  5, --> X90 867 716
'    _  _  _  _  _  _     _
| ||_|| ||_||_   |  |  ||_
  | _||_||_||_|  |  |  ||_|
                            
'
UNION
SELECT 6, --> 012 345 678
' _     _  _     _  _  _  _
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                            
'

;with ScanLine as
(
 SELECT 
  ID
  , 1 AS LineNumber
  , SUBSTRING( REPLACE( ScanNumber, ' ', '$'), PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) + 2, LEN( ScanNumber ) ) AS ScanNumber
  , SUBSTRING( REPLACE( ScanNumber, ' ', '$'), 1, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) ) AS LineString
 FROM @t
 
 UNION ALL
 
 SELECT 
  ID
  , LineNumber + 1 AS LineNumber
  , SUBSTRING( ScanNumber, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) + 2, LEN( ScanNumber ) ) AS ScanNumber
  , SUBSTRING( ScanNumber, 1, PATINDEX('%' + CHAR(13) + CHAR(10) + '%', ScanNumber ) ) AS LineString
 FROM ScanLine
 WHERE LEN(LineString) > 0 
)

---- SELECT * FROM ScanLine ORDER BY Id, LineNumber OPTION (MAXRECURSION 0);

, ScanDigit AS
(
 SELECT 
  Id
  , LineNumber
  , 1 AS CharNumber
  , SUBSTRING( REPLACE( REPLACE( LineString, CHAR(13), ''), CHAR(10), ''), 4, LEN(LineString)) AS LineString
  , SUBSTRING( REPLACE( REPLACE( LineString, CHAR(13), ''), CHAR(10), ''), 1, 3) AS CharString
 FROM ScanLine 
 WHERE LineNumber <= 3
 
 UNION ALL

 SELECT 
  Id
  , LineNumber
  , CharNumber + 1 AS CharNumber
  , SUBSTRING( LineString, 4, LEN(LineString)) AS LineString
  , SUBSTRING( LineString, 1, 3) AS CharString
 FROM ScanDigit 
 WHERE LEN(LineString) > 0
)

----- SELECT * FROM ScanDigit ORDER BY ID, CharNumber, LineNumber

, ScanNumber AS 
(
 SELECT 
  DISTINCT
  ID
  , CharNumber
  , ( 
   --REPLACE( 
     --REPLACE( 
       REPLACE( (
          SELECT ',' + CharString + REPLICATE('$', 3 - LEN(CharString) )
          FROM ScanDigit AS ConcanetChar
          WHERE ConcanetChar.Id = ScanDigit.Id
           AND ConcanetChar.CharNumber = ScanDigit.CharNumber
          ORDER BY LineNumber
          FOR XML PATH('')
         ), ',', '')
     --  , ' ', ' ')
     --, '
', '')
    ) AS CharString 
 FROM ScanDigit
)

---- SELECT * FROM ScanNumber ORDER BY ID, CharNumber OPTION (MAXRECURSION 0);

, DisplayNumber AS 
(
 SELECT 
  DISTINCT
  ID
  , CharNumber
  , (
   CASE CharString
    WHEN '$_$|$||_|' THEN '0'
    WHEN '$$$$$|$$|' THEN '1'
    WHEN '$_$$_||_$' THEN '2'
    WHEN '$_$$_|$_|' THEN '3'
    WHEN '$$$|_|$$|' THEN '4'
    WHEN '$_$|_$$_|' THEN '5'
    WHEN '$_$|_$|_|' THEN '6'
    WHEN '$_$$$|$$|' THEN '7'
    WHEN '$_$|_||_|' THEN '8'
    WHEN '$_$|_|$_|' THEN '9'
    WHEN ''    THEN ''
    ELSE 'X'
   END
   ) AS DisplayChar
 FROM ScanNumber
)

--- SELECT * FROM DisplayNumber ORDER BY ID, CharNumber OPTION (MAXRECURSION 0);

SELECT DISTINCT 
 ID
 , REPLACE( (
    SELECT ',' + DisplayChar
    FROM DisplayNumber AS Digit
    WHERE Digit.Id = DisplayNumber.Id
    ORDER BY CharNumber
    FOR XML PATH('')
     ), ',', '') AS ScanNumber
FROM DisplayNumber 
ORDER BY ID
OPTION (MAXRECURSION 0);

Calendar

SET NOCOUNT ON

SET LANGUAGE FRENCH

----;with MonthData AS 
----(
---- SELECT DATEADD(MM, 11, CAST('2009/01/01' AS DATETIME) ) AS Dt
 
---- UNION ALL
 
---- SELECT DATEADD(DD, 1, Dt) AS Dt
---- FROM MonthData
---- WHERE MONTH(Dt) = MONTH(GETDATE())
----)
----SELECT * FROM MonthData

----SET LANGUAGE us_english



--SET LANGUAGE FRENCH

DECLARE @tblCalendar TABLE (Mth INT, Yr INT) 

INSERT @tblCalendar(Mth, Yr) SELECT 8, 2009 
INSERT @tblCalendar(Mth, Yr) SELECT 2, 1900 
INSERT @tblCalendar(Mth, Yr) SELECT 10,1959 

DECLARE @tblCalendarData TABLE (CalDate DATETIME) 

INSERT INTO @tblCalendarData
 SELECT DATEADD(MM, ( Mth - 1 ), CAST( ( '01/01/' + CAST( Yr AS VARCHAR(4) ) ) AS DATETIME ) )
 FROM @tblCalendar
 
;with MonthData AS 
(
 SELECT CalDate, MONTH(CalDate) AS Mth, YEAR(CalDate) AS Yr, DATEPART(DW, CalDate) AS DW, DATEPART(WW, CalDate) AS WW
 FROM @tblCalendarData
 
 UNION ALL
 
 SELECT DATEADD(DD, 1, CalDate) AS CalDate, Mth, Yr, DATEPART(DW, DATEADD(DD, 1, CalDate)) AS DW, DATEPART(WW, DATEADD(DD, 1, CalDate)) AS WW
 FROM MonthData
 WHERE MONTH(CalDate) IN ( SELECT Mth FROM @tblCalendarData )
)
, MonthView AS
(
 SELECT 
  Yr
  , Mth
  , WW
  , [1] AS W1
  , [2] AS W2
  , [3] AS W3
  , [4] AS W4
  , [5] AS W5
  , [6] AS W6
  , [7] AS W7
 FROM 
 (
  SELECT CAST( DATEPART( DAY, CalDate) AS VARCHAR(2) ) AS CalDate, WW, DW, Mth, Yr
  FROM MonthData
 ) up
 PIVOT ( MIN(CalDate) FOR DW IN ( [1], [2], [3], [4], [5], [6], [7] ) ) AS pvt
)
, CalendarView AS
(
 SELECT 
  (
   '|'
   +  REPLICATE(' ', 4 - LEN( ISNULL( W1, '' ) ) ) + CAST( ISNULL( W1, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W2, '' ) ) ) + CAST( ISNULL( W2, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W3, '' ) ) ) + CAST( ISNULL( W3, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W4, '' ) ) ) + CAST( ISNULL( W4, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W5, '' ) ) ) + CAST( ISNULL( W5, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W6, '' ) ) ) + CAST( ISNULL( W6, '' ) AS VARCHAR(2) )
   + REPLICATE(' ', 4 - LEN( ISNULL( W7, '' ) ) ) + CAST( ISNULL( W7, '' ) AS VARCHAR(2) )
   + '|'
  ) AS Calender, Yr, Mth, WW
 FROM MonthView
 
 UNION ALL
 
 SELECT REPLICATE('-', 30), YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -3 AS WW
 FROM @tblCalendarData
 
 UNION ALL
 
 SELECT 
  (
     REPLICATE(' ', ( 28 - LEN( DateName( mm, CalDate ) + ' ' + CAST( Year(CalDate) AS VARCHAR(4) ) ) ) /2 ) 
   + DateName( mm, CalDate ) + ' ' + CAST( Year(CalDate) AS VARCHAR(4) ) 
  ) AS Calender
  , YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -2 AS WW
 FROM @tblCalendarData
 
 UNION ALL
 
 SELECT '|' + REPLICATE('=', 28) + '|' AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, -1 AS WW
 FROM @tblCalendarData
 
 UNION ALL
 
 SELECT '| Sun Mon Tue Wed Thu Fri Sat|' AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, 0 AS WW
 FROM @tblCalendarData
 
 UNION ALL
 
 SELECT REPLICATE('=', 30) AS Calender, YEAR(CalDate) AS Yr, MONTH(CalDate) AS Mth, 999 AS WW
 FROM @tblCalendarData
)

SELECT 
 (
  CASE WW
   WHEN -2
    THEN '|' + Calender + REPLICATE(' ', (28 - LEN( RTRIM( Calender ) ) ) ) + '|'
   ELSE Calender
  END
 ) AS Calender
FROM CalendarView 
ORDER BY Yr, Mth, WW
OPTION (MAXRECURSION 0)

SET LANGUAGE us_english

Year Dates

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)

SELECT @FirstDateOfYear, @LastDateOfYear
-- 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 Not Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

Merge Query W/O Using Merge

DECLARE @tblSource Table ( ID INT, GroupID INT, Name VARCHAR(10) )
DECLARE @tblTarget Table ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO @tblSource VALUES (1,1,'a1')
INSERT INTO @tblSource VALUES (2,1,'a2')
INSERT INTO @tblSource VALUES (3,1,'a3')
INSERT INTO @tblSource VALUES (4,1,'a4')

INSERT INTO @tblTarget VALUES (1,1,'a1')
INSERT INTO @tblTarget VALUES (3,1,'a3')
INSERT INTO @tblTarget VALUES (5,1,'a5')
INSERT INTO @tblTarget VALUES (6,2,'b2')
INSERT INTO @tblTarget VALUES (7,2,'b1')

-----Delete Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NULL
 AND tblTarget.ID IS NOT NULL
 AND tblTarget.GroupID = 1
 
-----Update Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NOT NULL
 AND tblTarget.ID IS NOT NULL

-----Inserted Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE 
 tblSource.ID IS NOT NULL
 AND tblTarget.ID IS NULL
 AND tblSource.GroupID = 1

--SELECT * 
--FROM @tblSource AS tblSource
-- LEFT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT * 
--FROM @tblSource AS tblSource
-- RIGHT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT * 
--FROM @tblSource AS tblSource
-- FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

PIVOT

DECLARE @FromDate DATETIME
 DECLARE @ToDate   DATETIME
 
 ----------------
 SET NOCOUNT ON

 SET @FromDate = '10/1/2009'
 SET @ToDate   = '10/1/2010'
 
 CREATE Table #tblSchool ( SchoolName VARCHAR(20), [Date] DATETIME, NoOfBoys INT, NoOfGirls INT )  
 
 DECLARE @SqlQuery VARCHAR(MAX)
 DECLARE @Months   VARCHAR(MAX)
 DECLARE @DisplayMonths   VARCHAR(MAX)
 SET @SqlQuery = ''
   
 INSERT INTO #tblSchool VALUES ('aa','1/1/2010', 10, 15)  
 INSERT INTO #tblSchool VALUES ('aa','1/2/2010', 15, 17)  
 INSERT INTO #tblSchool VALUES ('aa','2/1/2010', 18, 19)  
 INSERT INTO #tblSchool VALUES ('aa','2/2/2010', 21, 22)  
 
 INSERT INTO #tblSchool VALUES ('bb','1/1/2010', 11, 17)  
 INSERT INTO #tblSchool VALUES ('bb','1/2/2010', 16, 19)  
 INSERT INTO #tblSchool VALUES ('bb','2/1/2010', 19, 21)  
 INSERT INTO #tblSchool VALUES ('bb','2/2/2010', 25, 25)  
 
 ;with MonthData AS 
 (
  SELECT DATEADD(DD, 1 - DAY( @FromDate ), @FromDate ) AS MonthDates
  
  UNION ALL
  
  SELECT DATEADD(MM, 1, MonthDates ) AS MonthDates
  FROM MonthData
  WHERE MonthDates <= @ToDate
 )
 
 SELECT * 
 INTO #MonthData
 FROM MonthData
 ORDER BY MonthDates
 
 SELECT @Months = STUFF(
       (
        SELECT ', [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + ']'
        FROM #MonthData
        FOR XML PATH('')
       ),1,1,'')

 SELECT @DisplayMonths = STUFF(
       (
        SELECT ', ISNULL( [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + '], 0 ) AS [' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 1, 3 )  
           + ' ' + SUBSTRING( CONVERT( VARCHAR(20), MonthDates ), 8, 4 )
           + ']'
        FROM #MonthData
        FOR XML PATH('')
       ),1,1,'')
        
 SET @SqlQuery ='SELECT SchoolName, ' + @DisplayMonths + '
     FROM  
     (  
      SELECT   
       SchoolName  
       , (  
        SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 1, 3 )  
        + '' '' + SUBSTRING( CONVERT( VARCHAR(20), [Date] ), 8, 4 )  
         ) AS BillMonth  
       , ( NoOfBoys + NoOfGirls ) AS TotalStudent
      FROM #tblSchool  
      WHERE #tblSchool.[Date] BETWEEN ''' + CAST( @FromDate AS VARCHAR(20) ) + ''' AND ''' + CAST( @ToDate AS VARCHAR(20) ) + '''
     ) AS ItemDetail  
     PIVOT ( SUM(TotalStudent) FOR BillMonth IN ( ' + @Months + ' ) ) AS pvt '

 PRINT ( @SqlQuery )
 EXECUTE ( @SqlQuery )

 DROP TABLE #tblSchool
 DROP TABLE #MonthData

Find Field into Datatable

DECLARE @FieldName VARCHAR(255)

SET @FieldName = 'YTD'

SELECT 
 Obj.Name AS ObjectName
 , ( 
  CASE Obj.XType 
   WHEN 'F' then 'foreign keys'
   WHEN 'FN' then 'user defined functions'
   WHEN 'TF' then 'user defined table functions'
   WHEN 'P' then 'stored procedures'
   WHEN 'PK' then 'primary keys'
   WHEN 'S' then 'system tables'
   WHEN 'TR' then 'triggers'
   WHEN 'U' then 'user table'
   ELSE Obj.XType
  END 
  ) AS ObjectType
 , Col.Name AS FieldName
 , Col.xType as FieldType
 , Type_Name(Col.xType) AS FieldTypeName
 , Col.Length as FieldLength
 , Col.ColStat & 1 AS IsIdentity  
FROM SysColumns AS Col
 INNER JOIN SysObjects AS Obj ON Obj.Id = Col.Id
WHERE Col.Name like '%' + @FieldName + '%'
ORDER BY ObjectType, ObjectName

Comma Seprate String

DECLARE @Table TABLE (ID1 INT, ID2 INT, ID3 INT, Name VARCHAR(10))

INSERT INTO @Table VALUES (1,1,1,'a')
INSERT INTO @Table VALUES (1,1,1,'b')
INSERT INTO @Table VALUES (2,1,1,'a')
INSERT INTO @Table VALUES (2,1,1,'b')
INSERT INTO @Table VALUES (3,1,1,'a')
INSERT INTO @Table VALUES (3,1,1,'b')

SELECT * FROM @Table

SELECT
 DISTINCT
 ID1
 , ID2
 , ID3
 , REPLACE(
    REPLACE(
      REPLACE(
        CAST((
          SELECT Name 
          FROM @Table AS B
          WHERE b.ID1 = a.ID1
           AND b.ID2 = a.ID2
           AND b.ID3 = a.ID3
          FOR XML PATH('')
         ) AS VARCHAR(MAX) )
        , ''
        , ', '
       )
    , ''
    , ''
    )
   , ''
   , ''
   ) AS Name
FROM @Table AS A

SELECT
 DISTINCT
 ID1
 , ID2
 , ID3
 , REPLACE(
    (
     SELECT Name AS 'data()'
     FROM @Table AS B
     WHERE b.ID1 = a.ID1
      AND b.ID2 = a.ID2
      AND b.ID3 = a.ID3
     FOR XML PATH('')
    )
    , ' '
    , ', '
   ) AS Name
FROM @Table AS A

SELECT STUFF((
    SELECT ',' + s.Name
    FROM @Table s
    ORDER BY s.Name
    FOR XML PATH('')
   ),1,1,'') AS CSV


SELECT Name AS 'data()'
FROM @Table
FOR XML PATH('')

Find Content into Database

SET NOCOUNT ON

DECLARE @NoOfCount INT
DECLARE @CurrentId INT
DECLARE @ProecdueName VARCHAR(100)
DECLARE @FindContent  VARCHAR(100)
DECLARE @XType  VARCHAR(10)

SET @CurrentId = 1
SET @ProecdueName = ''
SET @FindContent  = 'procAlertsUpdateAlert' -- Type Content for find

DECLARE @Table TABLE
(
 ID    INT IDENTITY(1,1)
 , ProecdueName VARCHAR(100)
 , XType   VARCHAR(10)
)

DECLARE @TableProcedureContent TABLE
(
 Content VARCHAR(MAX)
)

INSERT INTO @Table 
 SELECT Name, XType
 FROM SysObjects 
 WHERE XType IN ( 'FN', 'IF', 'TF', 'P', 'TR', 'V')
  AND Category = 0
 ORDER BY XType, Name

SELECT @NoOfCount = COUNT(ID) FROM @Table

WHILE ( @CurrentId <= @NoOfCount )
 BEGIN
  DELETE FROM @TableProcedureContent
  
  SELECT @ProecdueName = ProecdueName
   , @XType = XType
  FROM @Table
  WHERE Id = @CurrentId
  
  INSERT INTO @TableProcedureContent
   EXECUTE sp_helptext @ProecdueName
   
  IF ( EXISTS( 
      SELECT * 
      FROM @TableProcedureContent 
      WHERE Content Like '%' + @FindContent + '%'
     ) )
   BEGIN
    PRINT ( CASE @XType 
       WHEN 'P'  THEN 'Procedure' 
       WHEN 'FN' THEN 'Function' 
       WHEN 'TR' THEN 'Trigger' 
       ELSE '' 
      END ) + ' ' + @ProecdueName
   END
  
  SET @CurrentId = @CurrentId + 1
 END

Convert Comma Seprate String to Row

DECLARE @TableA TABLE (Name VARCHAR(100) )
INSERT INTO @TableA VALUES ('MARY,MA,JENNY')

DECLARE @TableB TABLE (Name VARCHAR(100), DISTRICT_NO INT )
INSERT INTO @TableB VALUES ('MARY', 2)
INSERT INTO @TableB VALUES ('YY', 3)
INSERT INTO @TableB VALUES ('JOHN', 4)

DECLARE @XML XML

SELECT @XML = CAST( (
      SELECT '<name>' + REPLACE(Name, ',', '</name><name>') + '</name>'
      FROM @TableA
     ) AS XML )

SELECT *
FROM @TableB AS TableB
 INNER JOIN (
     SELECT
      TableA.value('.', 'VARCHAR(100)') AS Name
     FROM @XML.nodes('Name')e(TableA)
    ) AS TableA ON TableA.Name = TableB.Name

PIVOT IN Multipal Column

DECLARE @Table TABLE ( OrderId INT, ItemId INT, ItemQty INT, ItemValue INT)

INSERT INTO @Table VALUES (1,1,1,10)
INSERT INTO @Table VALUES (1,2,2,20)
INSERT INTO @Table VALUES (1,3,3,30)

INSERT INTO @Table VALUES (2,1,1,11)
INSERT INTO @Table VALUES (2,2,2,21)
INSERT INTO @Table VALUES (2,3,3,31)

INSERT INTO @Table VALUES (3,1,1,12)
INSERT INTO @Table VALUES (3,2,2,22)
INSERT INTO @Table VALUES (3,3,3,32)

SELECT
    OrderId
    , SUM([Q1]) AS Q1
    , SUM([Q2]) AS Q2
    , SUM([Q3]) AS Q3
    , SUM([V1]) AS V1
    , SUM([V2]) AS V2
    , SUM([V3]) AS V3
FROM
(
    SELECT
        OrderId
        , 'Q' + CAST( ItemId AS VARCHAR(10) ) AS QtyItemCd
        , ItemQty
        , 'V' + CAST( ItemId AS VARCHAR(10) ) AS ValueItemCd
        , ItemValue
    FROM @Table
) AS OrderDetail
PIVOT ( SUM(ItemQty)   FOR QtyItemCd   IN ( [Q1], [Q2], [Q3] ) ) AS pvtQty
PIVOT ( SUM(ItemValue) FOR ValueItemCd IN ( [V1], [V2], [V3] ) ) AS pvtValue
GROUP BY OrderId
ORDER BY OrderId