Anyone heard of COALESCE? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Anyone heard of COALESCE?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-06-25 : 16:12:35

I have this, but it is not working:

INSERT INTO tblTextFile (textfile)
SELECT COALESCE('þ',CaseNumber,'þ','1','þ','0','þ',LastName,'þ',FirstName,'þ',MailAddress,'þ',MailCity,'þ',MailState,'þ',MailZipCode,'þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ','þ',PropAddress,'þ',PropCity,'þ',PropState,'þ',PropZipCode,'þ','þ',CONVERT(varchar,MortAmount),'þ',CONVERT(varchar,PaidUpFront),'þ',HoldingMor,'þ',ServingMor,'þ',EndorseMonth+'/'+EndorseDay+'/'+EndorseYear,'þ',Term,'þ',MaturityMonth+'/'+MaturityDay+'/'+MaturityYear,'þ',EncumMonth+'/'+EncumDay+'/'+EncumYear,'þ',CONVERT(varchar,RefundAmount),'þ','11','þ','þ','þ','þ','þ')
FROM tblAbove1500

I want all of the data to be put into one column in another table. Any help?

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-25 : 16:14:34

Just concatenate the columns together with +.

INSERT INTO tblTextFile (textfile)
SELECT Column1 + ',' + Column2 + ...
FROM Table1

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 16:15:47

coalesce takes the first non-null entry in the list - which in your case is always 'þ'.

I think you want 'þ'+CaseNumber+'þ'+'1'+'þ'+'0'+'þ'+LastName+'þ'+FirstName+....

use coalesce for the individual columns if they are nullable
+coalesce(CaseNumber,'')+

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-25 : 16:18:18

Wait... it looks like you have a delimiter: þ

Why don't you just export the table with that as the delimiter??

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources