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
0 comments:
Post a Comment