If Blank fields use data in field above

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If Blank fields use data in field above

Author

Topic

leslieb
Starting Member

23 Posts

Posted - 2008-08-28 : 01:46:58

Being a newbie I am still learning.
I have a data base that has blank fileds and would like to populate with the data in the field above.
Current data(the commas are the deliminators):
3399 ,24 ,12200 ,000000
,24 ,440485 ,000000
,23 ,109700 ,000000
,24 ,102840 ,000000
3489 ,24 ,68310 ,000000
3539 ,23 ,3700 ,000003
5209 ,24 ,210215 ,000000
,23 ,656900 ,000000
5509 ,23 ,27400 ,000000
5709 ,24 ,383460 ,000000

Would like it to look like:
3399 24 12200 000000
3399 24 440485 000000
3399 23 109700 000000
3399 24 102840 000000
3489 24 68310 000000
3539 23 3700 000003
5209 24 210215 000000
5209 23 656900 000000
5509 23 27400 000000
5709 24 383460 000000

Is there any way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 03:59:31

whats the primary key of your table?

leslieb
Starting Member

23 Posts

Posted - 2008-08-28 : 17:52:35

Do not have one. The data will go in and then there will be a conversion of the first column number and then the data wil be exported.

The next day new data will replace the old data.

We are using SQL to do the conversion.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-28 : 18:52:56

Suggested steps:
1. Add an identity primary key to the table
2. Run sql something like this:

update t1
set a = CASE when isNull(a,'') = '' THEN
(Select a from test1 t2
where id in (select max(id) FROM test1 t3 where t3.id < t1.id and isNull(a,'') <> '')
)
ELSE a
END
from test1 t1

leslieb
Starting Member

23 Posts

Posted - 2008-09-01 : 22:48:03

Still confused.

The column names are

C0 C1 C2 C3
3399 ,24 ,12200 ,000000
,24 ,440485 ,000000
,23 ,109700 ,000000
,24 ,102840 ,000000
3489 ,24 ,68310 ,000000
3539 ,23 ,3700 ,000003

I also have a rownumber called 'rno'
The file is called 'chequing'

How does this fit into the script you listed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources