Category Archives: SQL Code Snippets

SQL Server : Combine two rows based on the values of one column

Posted on by
Reply

I came across a requirement to combine rows based on the values of a column. This is an interesting query so thought of sharing it with a simple example online.

Here is a table with players from various teams and their email addresses. PlayerEmails

The task is to create a mailing list for each team so that it returns the result belowEmailResult

Here is the script to create the table, insert sample data and the query to select the mailing lists.

PlayerEmailScript

Here is the code text if you want to copy and paste:

create table PlayerEmails
(
Team varchar(100),
Player varchar(100),
Email varchar(200),
)
go
insert PlayerEmails (Team,Player,Email)
select 'SL','Mahela', 'Mahela@email.com'
union
select 'SL','Sanga', 'Sanga@email.com'
union
select 'SL','Dilshan', 'Dilshan@email.com'
union
select 'WI','Gayle', 'Gayle@email.com'
union
select 'Aus','Waugh', 'Waugh@email.com'
union
select 'Aus','Warne', 'Warne@email.com'
go
select    Team,
replace(
(    select Email as [data()]
from    PlayerEmails t2
where    t2.Team = t1.Team
for xml path('')
)
, ' ', ';'
) as EmailAddress
from    PlayerEmails t1
group by Team
go
drop table PlayerEmails
go

SQL Server Error : The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Posted on by
3

Two of my colleagues had the following error recently and I helped them resolve it. So I thought of writing a blog on how to prevent this error with a simple example.

The error is :

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The following code will throw this exception:


--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1)    -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
insert error(error)
select @error
if @@trancount > 0
rollback
end catch

The exception is thrown because it is trying to write to a table within the catch block before rolling back the transaction. The solution is to move the insert statement after the rollback. And it is really important to log error messages after the rollback bacause the logged messages also will be rolled back with the subsequent rollback tran statement. The following code will not throw the exception:


--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1) -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
if @@trancount > 0
rollback
insert error(error)
select @error
end catch

SQL Server : Recursive CTE (Hierarchy Example)

Posted on by
Reply

create table #temp
(
id int,
name varchar(30),
parent_id int
)

insert #temp ( id, name, parent_id )
values  ( 1, 'A', null )
, ( 2, 'B', null )
, ( 3, 'C', null )
, ( 4, 'AA', 1 )
, ( 5, 'BB', 2 )
, ( 6, 'AAA', 4 )
, ( 7, 'AAAA', 6 )

select * from #temp

image

;with cte (id,name,parent)
as
(
select id, name,cast('' as varchar(30)) as parent
from #temp
where parent_id is null

union all

select c1.id,c1.name,c2.name as parent
from #temp c1
join cte c2 on c1.parent_id = c2.id
)

select id,name,parent from cte

image

SQL Server : Recursive CTE and Maximum Recursion

Posted on by
Reply

Default maximum recursion is 100. This can be altered by setting the maxrecursion as an option. If the recursion has gone past the maximum then you will receive the following error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Here is an example with maximum recursion set to 1000.

;with cte(N) as
(
select 1 as N
union all
select cte.N + 1
from cte
where cte.N <1000
)
select * from cte
option (maxrecursion 1000)

SQL Server : Output Clause with UPDATE

Posted on by
Reply

declare @audit table(Id int,value int,value2 int,Diff int,value3 int)

create table Test (Id int identity(1,1), value int, value2 int)
create table Test2 (Id int identity(1,1), value int, value2 int,value3 int)

insert Test (value,value2)
values(5,6),(7,8),(9,10),(10,11)
insert Test2 (value,value2,value3)
values (120,130,30),(5,4,0),(7,8,1),(9,3,2),(12,13,3)

select * from Test
image

select * from Test2
image

update Test
set value2 = t.value2 + t2.value2
output inserted.Id, inserted.value,inserted.value2,t2.value2,t2.value3 into @audit(Id,value,value2,Diff,value3)
from Test t
join Test2 t2 on t.value = t2.value

select * from Test
image

select * from Test2
image

select * from @audit
image

SQL Server : CROSS APPLY & OUTER APPLY

Posted on by
Reply

This article explains cross apply and outer apply.

(Source : http://sqlserverplanet.com/sql-2005/cross-apply-explained)

My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today.

I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper.

SELECT
SalesOrderID           = soh.SalesOrderID
,OrderDate              = soh.OrderDate
,MaxUnitPrice           = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM AdventureWorks.Sales.SalesOrderHeader AS soh

It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner:

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN
(
SELECT
max_unit_price = MAX(sod.UnitPrice),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic inside the derived table got so complex, that it would not limit the result set of the derived table by inferring the correlation first. This made this kind of query sometimes impractical.

Luckily, this is where the CROSS APPLY steps in so nicely. It gives us the best of both worlds by allowing us to correlate AND not have the query embedded in the select list:

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

The other advantage this has over the correlated sub query is when we want to add more columns in our SELECT list, we do not have to completely repeat the entire query. We still have it in one place, making it somewhat modular. So instead of this:

SELECT
SalesOrderID           = soh.SalesOrderID
,OrderDate              = soh.OrderDate
,MaxUnitPrice           = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) -- 1
,SumLineTotal           = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) -- 2
FROM AdventureWorks.Sales.SalesOrderHeader AS soh

We have this:

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
,sod.sum_line_total
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
,sum_line_total = SUM(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

As for the execution plans, in my experience CROSS APPLY has always won. Not always by a lot, but it still wins.

So what is OUTER APPLY? It’s equivalent to a left join on the derived table.

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
LEFT JOIN
(
SELECT
max_unit_price = MAX(sod.UnitPrice),
SalesOrderID
FROM Sales.SalesOrderDetail AS sod
GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID
SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
OUTER APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

SQL Server: Split Function

Posted on by
Reply

create FUNCTION Split(@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx – 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) – @idx)       
        if len(@String) = 0 break       
    end   
return
end;

select items, count(items)
from table1 t1
cross apply dbo.split(t1.id, ‘,’)
group by items

Source: http://stackoverflow.com/questions/12920013/t-sql-split-and-aggregate-comma-separated-values?rq=1

SQL Server : BCP Out and Bulk Insert – Simple Example

Posted on by
Reply

Export and Import Data to/from files

-- Export Data to csv file

declare @cmd varchar(8000)

set @cmd = N'bcp "SELECT * from DB..TableName" queryout C:\MP.csv -T -c -t,'

exec sys.xp_cmdshell @cmd

-- Import Data

BULK INSERT TableName

FROM 'C:\MP.csv'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

);

PS:
BCP utility requires sys.xp_cmdshell system procedure to execute. To execute sys.xp_cmdshell procedure you need to enable xp_cmdshell feature. By default this is disabled.

You can enable this by running the following commands:


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable xp_cmdshell feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO