July 25, 2005 at 12:00 pm
I have the following code which is essentially inserting into a on column table. It goes thru a loop and then inserts some records after the loop. My problem is that the records inserted after the loop is finished do not always get inserted at the end of the table as you would expect. They do sometimes but not every time. THe table is dropped and recreated everytime. There are no keys and no indexes. I'm simply trying to created a customized structure for exporting using bcp to an xml document. What is really blowing my mind is the records not appearing in the order I expect.
Here is the code
use hqcontrol
if exists (select * from hqcontrol.dbo.sysobjects where id = object_id(N'hqcontrol.[dbo].[xmloutput]') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
print 'Table Exists'
drop table hqcontrol.[dbo].[xmloutput]
end
else
begin
print 'Table Does not exits'
end
go
create table hqcontrol.[dbo].[xmloutput]
( xmlLine varchar(1000)
)
go
use tagcom
declare @counter int, @dept varchar(3),@deptDesc varchar(36),@catDesc varchar(36)
declare @noCats int, @catid varchar(6),@subcat varchar(4),@upc varchar(13)
declare @mastDept varchar(3), @mastCat varchar(6),@mastsubcat varchar(4)
select distinct ct.category,im.store_pos_department,ct.description as catDesc,sd.description as DeptDesc,upc_ean as upc
into #tmpcats from item_master im
join category ct on ct.category = im.category
left outer join store_department sd on sd.store_pos_department = im.store_pos_department
where ct.kss_flag = 1 and ct.record_status <> 3
order by 1,2,5
select distinct substring(store_pos_department,2,2) as Dept,substring(isnull(store_pos_department,'000'),2,2) + substring(category,1,4) as Categoryid,substring(category,5,4) as subCategoryId,catdesc,deptdesc,upc
into #catsinter from #tmpcats
select identity(int,1,1) as rowid, Dept,categoryid,subcategoryid,upc,catdesc,deptdesc
into #cats from #catsinter
set @noCats = @@rowcount
select @mastdept = ''
select @mastcat = ''
set @mastsubcat = ''
insert into hqcontrol.dbo.XmlOutput SELECT
'<ImportData xmlns="xxx.xxx.com:CC3">'
insert into hqcontrol.dbo.XmlOutput SELECT
' <Summary userName="Some Company" description="Product List" creationTime="' + convert(varchar(30),getdate()) + '">'
insert into hqcontrol.dbo.XmlOutput SELECT
' </Summary>'
set @counter = 1
while @counter <= @nocats
begin
select @dept = dept, @deptDesc = rtrim(ltrim(deptDesc)),@catid = categoryid,@subcat = subcategoryid,@catdesc = rtrim(ltrim(catDesc)),@upc = upc from #cats where rowid = @counter
if @mastsubcat <> @subcat and @mastsubcat <> ''
begin
insert into hqcontrol.dbo.XmlOutput select ' </SubCategory>'
end
else
begin
if (@mastsubcat = @subcat and @mastsubcat <> '') and (@mastcat <> @catid and @mastcat <> '')
begin
insert into hqcontrol.dbo.XmlOutput select ' </SubCategory>'
end
end
if @mastcat <> @catid and @mastcat <> ''
begin
insert into hqcontrol.dbo.XmlOutput select ' </Category>'
end
if @mastDept <> @dept and @mastDept <> ''
begin
insert into hqcontrol.dbo.xmloutput select '</Department>'
end
if @mastDept <> @dept
begin
insert into hqcontrol.dbo.XmlOutput SELECT
'<Department Id="' + @dept + '" name="' + @DeptDesc + '">'
set @mastDept = @dept
end
if @mastCat <> @catid
begin
insert into hqcontrol.dbo.XmlOutput SELECT ' <Category Id="' + @catid + '">';
insert into hqcontrol.dbo.XmlOutput SELECT ' <SubCategory Id="' + @subcat + '" name="' + @catDesc + '">'
set @mastCat = @catid
set @mastsubcat = @subcat
end
else
begin
if @mastsubcat <> @subcat
begin
insert into hqcontrol.dbo.XmlOutput SELECT
' <SubCategory Id="' + @subcat + '" name="' + @catDesc + '">'
set @mastsubcat = @subcat
end
end
insert into hqcontrol.dbo.XmlOutput select ' <ProductSubCategory ProductId="' + @upc + '" />'
set @counter = @counter + 1
end
--These lines don't always end up at the end of the table
--and I think they should--Where am I going wrong on my
--thinking
insert into hqcontrol.dbo.XmlOutput select ' </SubCategory>'
insert into hqcontrol.dbo.XmlOutput select ' </Category>'
insert into hqcontrol.dbo.xmloutput select '</Department >'
insert into hqcontrol.dbo.XmlOutput SELECT '</PSImportData>'
July 25, 2005 at 12:09 pm
There is no concept of row order in an SQL Table (SET). If you need them in certain order, add an order column and when you perform a select use that column in the order by!
* Noel
July 25, 2005 at 12:09 pm
You're expecting these rows to appear in a certain order simply by the way you're inserting them? If that's the case, in general, with a relational DB you can't make that assumption as the physical storage can be implemented in any manner so long as the SQL parses correctly and returns accurate results. Hence the use of the ORDER BY clause in order to sort the data upon query.
K. Brian Kelley
@kbriankelley
July 25, 2005 at 12:12 pm
Tables in a relational database don't have any inherent order. If you want the rows back in a particular order, you need to provide a column with data in it which can be used in an 'order by' clause when you retrieve the data. In your case, it sounds like you need a sequence number (like an identity column) that will preserve the order you want.
July 25, 2005 at 12:39 pm
Thanks for the input.
I guess I knew that all along, I just didn't want to accept it. I'll try the identity thing. It just seems strange that I do get it in the correct order some of the time!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply