TableDiff.exe - SQL Server Comparison Utility

The tablediff.exe is very useful command line utility to compare two tables from SQL Server instances. 

Tablediff.exe utility features

·         Comparison between two tables from SQL Server instance. 

·         Row by row fast comparison. 

·         Perform column level comparison. 

·         Easily generate change script between production and development SQL server instances.

·         Log result can be captured as output file or database table.

Reference : http://msdn.microsoft.com/en-us/library/ms162843.aspx

SQL Puzzle #8

We need a single select statement to get the count of particular character on a string.

For Eg.,

DECLARE @string varchar(50) = 'Microsoft SQL Server',
        @SearchChar  char(1)     = 'r'

Its result should be '3'

SELECT LEN(@string) - LEN(REPLACE(@string,@SearchChar,''))

SELECT DATALENGTH(REPLACE  (@string, @SearchChar, @SearchChar+@SearchChar)) - DATALENGTH (@string) As [Character Count]

SQL Puzzle #7

We have a table variable say MyTable, structure like below.

declare @MyTable Table
(
TrnDate Date, -- Transaction Date
CrDb char(1),  -- Credit/Debit
Amount  money
)

-- Data population
insert into @MyTable values
('01-jul-2012','C',1000),('02-jul-2012','D',200),('03-jul-2012','C',2500),('04-jul-2012','C',800),('05-jul-2012','D',5000)

--select * from @MyTable

we need a single query to get the result as below.

TrnDate    CrDb Amount                Balance
---------- ---- --------------------- ---------------------
2012-07-01 C    1000.00               1000.00
2012-07-02 D    200.00                800.00
2012-07-03 C    2500.00               3300.00
2012-07-04 C    800.00                4100.00
2012-07-05 D    5000.00               -900.00

select TrnDate,CrDb,a.Amount,t.Balance
from @MyTable a
cross apply
(
select sum(case when crdb = 'C' then amount else amount*-1 end) as Balance
from @MyTable b
where  b.TrnDate <= a.TrnDate
) t

SQL Puzzle #6

We have a table say myTable and looks like below.

ID Name Sal
1 a         1000
1 a         2000
2 b         3000
2 b         2000
3 c         2000

And need a query to get the result as below.

ID Name Sal1 Sal2
1 a 1000 2000
2 b 3000 2000
3 c 2000 0



Ans:

Declare  @testTable Table
(
ID int,
Name varchar(50),
Sal money
)
Insert into @testTable
Values
(1, 'a', 1000),
(1, 'a', 2000),
(2, 'b', 3000),
(2, 'b', 2000),
(3, 'c', 2000)


select ID,Name,isnull([sal1],0) As [sal1],isnull([sal2],0) As [sal2]
from
(
     select id,name,sal,'Sal'+cast(ROW_NUMBER() over (partition by id,name order by id,name) as varchar(10)) as rownum
     from @testTable
) sources
pivot
(
     sum(sal)
     For rownum in([sal1],[sal2])
) pvt


NB: We can also do it with column dynamically..

SQL Puzzle #5

We have a table, say MyTable and data spreading looks like as follow;

Col1       Col2       Col3       Col4
1              A             B              C      
2              E              F              G      
3              I               J               K      
4              M            N             O    

And need a query to get the output as below;

Col1       MergedCol
1              A      
1              B      
1              C      
2              E      
2              F      
2              G      
3              I      
3              J      
3              K      
4              M      
4              N      
4              O    

And I have a simple strait forward query to get this result.
SELECT col1,col2 from MyTable
Union ALL
SELECT col1,col3 from MyTable
Union ALL
SELECT col1,col4 from MyTable
Union ALL
SELECT col1,col5 from MyTable




Ans:
Create Table TblTest
(
Col1 Int,
Col2 Varchar(50),
Col3 Varchar(50),
Col4 Varchar(50),
)


Insert Into TblTest Values (1,'A','B','C'),(2,'E','F','G'),(3,'I','J','K'),(4,'M','N','O')

Select * From TblTest

Select Col1,Upt.ColNew From TblTest UnPivot (ColNew For ColNewSt In (Col2,Col3,Col4))AS UPT

SQL Puzzle #4 - Comma separated value split up without using loop.

We have a table variable named @TableVar

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Qn: Write a query to get the result as shown below without using loop.

  Data
-------------
2012-05-01
2012-05-05
2012-04-13


Ans :

DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)




Another Method :

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Declare @Str AS Varchar(Max)
Select @Str = 'Select ' +'''' + REPLACE (Data,',',' ''UNION Select ''')  FRom @TableVar
Set @Str = @Str + ''''
Exec (@Str)

SQL Puzzle #3 - How to delete all the records from the entire table in a database using single line query?

How to delete all the records from the entire table in a database using single line query?


Ans : sp_MSForEachTable "Delete ?"

Try it another method;

DECLARE @str VARCHAR(MAX);
SELECT @str =
(
SUBSTRING
(
(SELECT 'DELETE FROM ' + TABLE_NAME + ';' FROM INFORMATION_SCHEMA.TABLES
FOR XML PATH('')),1,99999
)
)
EXEC (@str)