June 2009 - Posts

Export to Excel - data length exceeds 255 characters

 

If you use Data Transaformation Services to send data to EXCEL, you may get error about the size of the cell if length of data exceeds 255 characters. This KB from microsoft explains how to solve it

http://support.microsoft.com/default.aspx?scid=kb;en-us;281517

Posted by Madhivanan with no comments
Filed under: , ,

Quirky update in sql server

 

When
you use variables in update statement and update the columns with the values of variables which keep on changing for each row, you can simulate many things that are done using Loop or a Cursor

 

declare @temp table
(
product_name varchar(100),
Value int,
sequence int
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 25 , 0)
insert into @temp values('product7' , 25 , 0)
insert into @temp values('product8' , 26 , 0)

declare @value int,@sequence int, @runningtotal int,@product_name varchar(100)

--Serial No for all values

set @sequence=0

update @temp
set @sequence=sequence =@sequence+1
select * from @temp

--Serial No for each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence else @sequence+1 end,@value=value

select * from @temp

--Reset Serial No in each value

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence+1 else 1 end,@value=value

select * from @temp

--Running total

set @sequence=0

update @temp
set @sequence=sequence =@sequence+value
select * from @temp

--Generate data in csv format

GO

 

declare @value int,@sequence varchar(100), @product_name varchar(100)

declare @temp table
(
product_name varchar(100),
Value int,
sequence varchar(100)
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 26 , 0)

 

set @product_name=''

update @temp
set sequence=@product_name ,
@product_name=case when @value=value then @product_name+','+product_name else product_name end,@value=value

select value,max(sequence) as sequence from @temp
group by value

The term "Quirky update" is referred in these articles
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

 

Posted by Madhivanan with 2 comment(s)