Error Generating CSV string - 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

Transact-SQL (2000)

Error Generating CSV string

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 01:53:16

Hi All,
Can anybody tell me why Update statement is not working.

create table #workingtable (
JobNumber VARCHAR(10),
FullName varchar(50),
list varchar(8000))

insert into #workingtable (JobNumber,FullName)
SELECT '007401','NAT'
SELECT '007399','VEN'
SELECT '007398','Mat'
SELECT '007397','Mat'
SELECT '007396','Mat'
SELECT '007393','Mel'
SELECT '007392','VEN'
SELECT '007391','PAT'
SELECT '007390','PAT'
SELECT '007389','VER'
SELECT '007388','Dam'
SELECT '007387','Mat'
SELECT '007386','VER'
SELECT '007385','NAT'
SELECT '007384','NAT'
SELECT '007383','PAT'
SELECT '007382','KEL'
SELECT '007381','Reb'
SELECT '007380','HEL'
SELECT '007380','KEL'
SELECT '007380','KEL'
SELECT '007379','VER'
SELECT '007378','HEL'

declare @list varchar(8000),@lasti varchar(10)

select @list = '', @lasti = ''

update #workingtable
set
@list = list = case when convert(int,@lasti) <> convert(int,JobNumber) then FullName else @list + ',' + FullName end,
@lasti = JobNumber

select * from #workingtable

drop table #workingtable

Thanks

mk_garg

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-19 : 02:08:50

What error are you getting? Assuming that you're trying to run this in QA, it should give you a line number that you can start looking for the problem in.

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-19 : 02:10:45

I'd like to help. I ran the T-SQL above (after adding UNION ALL between the SELECTS). Seems to do something.

You don't mention the result you are trying to get or what is wrong with the result the UPDATE renders.

Sam

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 02:16:55

It is not giving error but it is not joining any names although some jobnumbers are same.
Thanks

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 02:18:28

FOR '007380','HEL' result should be

007380,HEL
007380,HEL,KEL
007380,HEL,KEL,KEL

Thanks

mk_garg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 02:23:06

Your insert is not inserting into #workingtable. Change it to this:

insert into #workingtable (JobNumber,FullName) SELECT '007401','NAT'
insert into #workingtable (JobNumber,FullName) SELECT '007399','VEN'
insert into #workingtable (JobNumber,FullName) SELECT '007398','Mat'
insert into #workingtable (JobNumber,FullName) SELECT '007397','Mat'
insert into #workingtable (JobNumber,FullName) SELECT '007396','Mat'
insert into #workingtable (JobNumber,FullName) SELECT '007393','Mel'
insert into #workingtable (JobNumber,FullName) SELECT '007392','VEN'
insert into #workingtable (JobNumber,FullName) SELECT '007391','PAT'
insert into #workingtable (JobNumber,FullName) SELECT '007390','PAT'
insert into #workingtable (JobNumber,FullName) SELECT '007389','VER'
insert into #workingtable (JobNumber,FullName) SELECT '007388','Dam'
insert into #workingtable (JobNumber,FullName) SELECT '007387','Mat'
insert into #workingtable (JobNumber,FullName) SELECT '007386','VER'
insert into #workingtable (JobNumber,FullName) SELECT '007385','NAT'
insert into #workingtable (JobNumber,FullName) SELECT '007384','NAT'
insert into #workingtable (JobNumber,FullName) SELECT '007383','PAT'
insert into #workingtable (JobNumber,FullName) SELECT '007382','KEL'
insert into #workingtable (JobNumber,FullName) SELECT '007381','Reb'
insert into #workingtable (JobNumber,FullName) SELECT '007380','HEL'
insert into #workingtable (JobNumber,FullName) SELECT '007380','KEL'
insert into #workingtable (JobNumber,FullName) SELECT '007380','KEL'
insert into #workingtable (JobNumber,FullName) SELECT '007379','VER'
insert into #workingtable (JobNumber,FullName) SELECT '007378','HEL'

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 02:28:18

I posted this data to make it easy for others.
Just forget to add UNION ALL in select statement.

Let me check it is working on my tables or not.

Thanks!

mk_garg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 02:48:29

If you do it the way I wrote, rows will be inserted in the order you select them. With UNION ALL, SQL will possibly run the selects in parallel if you have multiple CPU's and the actual order of rows in #working table may not match the order of the SELECT unions. In order for the update statement you are trying to work , it depends on SQL updateing rows in physical order. That is usually a safe assumption but on the other hand, there is really no defined physical order of a table without a clustered index.

Create the table like this
create table #workingtable (
ID int identity(1,1),
JobNumber VARCHAR(10),
FullName varchar(50),
list varchar(8000))

Then use your insert and select from it and see if the ID is ordered by descending JobNumber. If not the select unions did not order like you expected.

Another workaround would be to

insert into #workingtable (JobNumber,FullName)
SELECT * FROM
(
SELECT '007401' JobNumber,'NAT' FullName UNION ALL
SELECT '007399','VEN' UNION ALL
...
) x
order by JobNumber DESC

BTW: Your code example worked for me using the multiple inserts.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 02:58:02

Thanks all!
it is working!

mk_garg

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-19 : 04:28:42

It's working because you ordered the INSERT data. This isn't a long-term solution. SQL doesn't guarrantee the order of rows, when you add more data later, the UPDATE will break.

You need an UPDATE solution that orders the data before performing the update. Better yet, a set-based solution that doesn't depend on the order.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-19 : 18:18:25

I don't understand, why UPDATE will break.

I have getting data into temp table from database. before moving to temp i am soring on JobNumber.

Thanks

mk_garg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 18:23:27

If you build the temp table from scratch each time it should continue to work. Another note about what I wrote earlier about parallel execution of SELECT UNION. In thinking about it I realized that will not happen because unless you use UNION ALL, the result set of all SELECTS will be sorted and duplicates discarded, so you would not have to worry about arbitrary insert orders (but I don't know HOW the results will be sorted, just that they will - so you probably still want to specify your own ORDER BY like you are already doing)

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources