Solved

Microsoft SQL 2005 SQL Statement Error

Posted on 2008-10-24
2
227 Views
Last Modified: 2010-03-19
I am using below statement to add an new column into the table. The satement is running well, but showing null value instead of default value '0'. Please help

Select @SQL= 'ALTER TABLE #Row ADD ' + QUOTENAME(@ColumnText) + ' Numeric(18,3) Default 0'
Exec   (@SQL)
0
Comment
Question by:Mehram
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
Comment Utility
That is because simply adding the column with a default value does not necessarily insert zero values
into the existing records--the default kicks in when new records are added.

You need to follow up with an update:

UPDATE TableName SET ColumnName = 0 WHERE ColumnName IS NULL
0
 
LVL 1

Assisted Solution

by:isdi
isdi earned 250 total points
Comment Utility
Mathhew's answer is correct. However, just to clarify ...

In your case it would look like:

Select @SQL= 'ALTER TABLE #Row ADD ' + QUOTENAME(@ColumnText) + ' Numeric(18,3) Default 0'
Exec   (@SQL)
GO

SELECT @SQL= 'UPDATE #Row SET ' + @ColumnText + ' = 0 WHERE ' + @ColumnText IS NULL'
GO

This is assuming that #Row and @ColumnText are still in scope.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Display multiple images in report 12 81
BULK INSERT most recent CSV 19 15
How to simplify my SQL statement? 9 0
Test a query 23 0
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now