Update NULL records in all columns with any value which is NOT NULL in same column SQL Server 2005/2008

Today I gave solution for one strange problem in one of the forum; I thought to share that script with all of you. Requirement was something like below:

— Update all field of Table which is NULL

–NULL data should be populated with NOT NULL value of the same column

Well, this is somehow strange but it was needed so I quickly create one small script with the help of cursor, however, I always avoid cursor as long as possible. I didn’t find any other quick solution at that time.

–create table for demo

if OBJECT_ID(’emps’,‘U’) is not null drop table emps

CREATE TABLE [dbo].[emps](

      [Name] [varchar](50) NULL,

      [Dept] [varchar](10) NULL,

      [Company] [varchar](15) NULL

) ON [PRIMARY]

 

GO

–insert some data

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘CHEM’ UNION ALL

SELECT ‘RAJAN’,NULL,NULL UNION ALL

SELECT NULL,‘ACCT’,‘MAR’

GO

 

–script with cursor

declare @SQL nvarchar(max)

DECLARE @ColName VARCHaR(15)

set @SQL=

 

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=’emps’

 

OPEN FirstCur

FETCH FROM FirstCur INTO @ColName

 

WHILE @@FETCH_STATUS=0

BEGIN

      SET @SQL=@SQL+ ‘ Update Emps SET ‘ + @ColName + ‘ = (SELECT top 1 ‘ + @ColName + ‘ FROM emps where ‘ + @ColName + ‘ is not null) where ‘ + @ColName + ‘ is null; ‘

      FETCH NEXT FROM FirstCur INTO @ColName

END

print @sql

CLOSE FirstCur

DEALLOCATE FirstCur

exec sp_executeSQL @SQL

go

 

–CHECK DATA

select * from emps

Happy Coding!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

 

Advertisements

Scrollable Dynamic Cursor with FETCH_FIRST, FETCH_LAST, FETCH_PRIOR, FETCH_NEXT in SQL Server 2005

This is continuous topic of CURSOR. You can refer my past article on cursor at

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

http://ritesh-a-shah.blogspot.com/2009/03/forwardonly-and-fastforward-cursor.html

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

I gave definition of CURSOR and explained different type of CURSOR in my first article given above. Second article was for FORWARD_ONLY cursor and third one was for dynamic cross tab query with FAST_FORWARD cursor. Now this article will show you example of dynamic cursor which can move forward and backward as well. It will get fresh record set with every FETCH statement so that while fetching the record, we will be able to get fresh modified record by another user for our SELECT statement in SQL Server 2005.

Let us see it practically.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

DECLARE @Counter INT

SET @Counter =1

DECLARE @strName VARCHAR(200)

DECLARE @empCursor CURSOR

–declaring SCROLL cursor

–which will move first, last, forward and backward

SET @empCursor = CURSOR SCROLL FOR

SELECT Name FROM emps ORDER BY NAME

OPEN @empCursor

FETCH NEXT FROM @empCursor INTO @strName

–user of FETCH NEXT

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @strName + NEXT’

FETCH NEXT FROM @empCursor INTO @strName

SET @Counter = @Counter + 1

END

–user of FETCH PRIOR

WHILE (@Counter > 1)

BEGIN

FETCH PRIOR FROM @empCursor INTO @strName

PRINT @strName + PRIOR’

SET @Counter = @Counter 1

END

–user of FETCH FIRST

BEGIN

FETCH FIRST FROM @empCursor INTO @strName

PRINT @strName + FIRST’

END

–user of FETCH LAST

BEGIN

FETCH LAST FROM @empCursor INTO @strName

PRINT @strName + LAST’

END

CLOSE @empCursor

DEALLOCATE @empCursor

GO

Note: this is very resource consuming cursor so think twice before use it.

Reference: Ritesh Shah/Rashmika Vaghela

FORWARD_ONLY and FAST_FORWARD Cursor example in SQL Server 2005:

My previous article was on cursors, it has covered different types of cursor with its definition. You can refer it at:

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

As I promised in my above article that I will provide with some examples of cursor and here I am with example

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–script for FORWARD_ONLY Cursor

DECLARE @strName VARCHaR(15)

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR SELECT Name FROM emps

OPEN FirstCur

FETCH FROM FirstCur INTO @strName

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @strName

FETCH NEXT FROM FirstCur INTO @strName

END

CLOSE FirstCur

DEALLOCATE FirstCur

go

I have created one script for dynamic cross tab query in one of my previous article. I have used FAST_FORWARD cursor in that script. It will be nice example of cursor. You can refer it at

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

Reference: Ritesh Shah

What is CURSOR? Definition of CURSOR in SQL Server 2005:

CURSOR is a server side tool and completely different from ADO.NET’s cursor. It is giving row-by-row solution to the result set and let me tell you that SQL Server impressive with handling set of rows, not row-by-row. This is useful for those who came from procedural background and don’t much familiar with set-based relational algebra.

You can define CURSOR as read only or update but read only is fast as compare with update as read only will gives your data and won’t remember it.

Personally I used to avoid cursor as long as it is possible as it uses lots of resources of server and reduce the performance. Whenever it is possible use temp table, derived table, sub-query, CASE statement but finally you draw conclusion that any of the set-based operation won’t work for your problem than and than go for CURSOR solution but make sure you are not selecting more than necessary rows in CURSOR. Lesser the row, higher the performance!!!!

The main reason for writing this article is to help those who want to understand the concept of CURSOR and another reason CURSOR is not something that you can completely ignore. There may be some situation where you have to use CURSOR, may be some complex logic or dynamic code iteration especially while making code generator.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Steps for CURSOR:

DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

You can run following query for dynamic management function in-order to get information about CURSOR running on your server.

select * from sys.dm_exec_cursors(0)
go

I will give examples to make your concept more clearly about different type of CURSOR in my future article.

Reference: Ritesh Shah