Old habits, any suggestions? - 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)

Old habits, any suggestions?

Author

Topic

srad
Starting Member

39 Posts

Posted - 2002-03-22 : 05:04:12

I receive data in a .csv file that needs to be loaded into SQL Server tables. Each row in the file has a unique id but can appear more than once in the file. I want to insert the last occuring row in the file only.

i.e. Contents of text file
Code,Description,Price
1,Toothpaste,£1.00
2,Soap,£0.50
1,Toothpaste,£0.90

In the above example for code 1 i want to insert the row that's priced at £0.90 only.

At the moment i load the data into a table then create a temp table
Create Table #Temp(rowpos int not null, code int not null,
description varchar(50) not null, price int not null)

to insert the rows specifying the Max aggregate function.

insert into #Temp(rowpos, code, description, price)
select max(prodid), code, description, price
from loadtable
group by code, description, price

I then extract this into then final destination table. This does work fine but i wondered if there was a cleaner way of doing it.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-22 : 06:11:35

why not create a temp file, with an identity field as the key...with a seed value of 1, incrementing by 1. then dts the csv table into it. as the records are read from the csv file from the top down, the records created in the temp file will be automatically created in ascending sequence number.

then after the dts has completed, run a query to find the entry for each product code with the highest identity value.

select * from inputtable a,
(select distinct(b.productcode), max(b.idfield) as b_idfield from inputtable b
group by b.productcode) as c
where a.idfield = c.b_idfield

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 06:32:15

Andrew - Do you know if DTS will definitely insert the rows into the table in the same order they appear in the text file?

quote:
select max(prodid), code, description, price

srad - What is prodid?
It doesn't appear to be in the load table yet you are using it to select the row that you want (with max).

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-22 : 07:03:27

"Andrew - Do you know if DTS will definitely insert the rows into the table in the same order they appear in the text file?"

Nope!....but can you tell me on any way of opening a text file, and just picking records at random, given that when opening the file, one doesn't know where the start of any record is, other than the 1st record? if the records were accessed in a random order, how would DTS guarantee that all records are read.

only 2 simple ways to do so, read from the top down or read from the bottom up, skipping nothing inbetween.....despite MS's abilities, I doubt that even they would make DTS work in an unusual manner!

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 07:17:06

Sorry - didn't mean to come across as picky.

I agree that it almost certainly does read the data in the right (logical) order (ie. top to bottom). The concern is if you cannot guarantee it. However, I wouldn't trust any Microsoft product to do things logically

If you feel uncertain about it (and I suppose you could test it by importing a sequentially numbered text file) then you could assign row numbers to the text file itself before importing it - eg. with C++ or something.

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-22 : 07:54:51

Mr D....sorry for shouting back!

Jay99

468 Posts

Posted - 2002-03-22 : 08:43:17

Why not just skip the temp table all together?


insert prodtable(code, desc, price)
select code, desc, price
from
    loadtable lt
    left join prodtable pt
        on lt.code = pt.code
where
    pt.code is null and
    not exists (
        select 1
        from
            loadtable
        where
            prodid > lt.prodid )

update prodtable
set
    code = lt.code,
    description = lt.description,
    price = lt.price
from
    loadtable lt
    inner join prodtable pt
        on lt.code = pt.code
where
    not exists (
        select 1
        from
            loadtable
        where
            prodid > lt.prodid )

Jay
<O>

Edited by - Jay99 on 03/22/2002 08:43:48

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources