Sql 2000 Dts

[BGM]

Hello,

I have a table with data imported from an excel spreadsheet via DTS..

some rows are:

|name | place | cost | hours|
|  x  |   y   |   a  |   b  |

and i need them to be:

|name | place | value|
|  x  |   y   |   a  |
|  x  |   y   |   b  |

what is the easiest way i can achieve this? so i can go start looking it up

id like to incorporate it into my DTS package if possible...

thank you

 

[BGM]

bump :/

 

[Cardboard Hammer]

Is value cost * hours?

EDIT: Likely, the fastest way to do it would be to load from the worksheet into a staging table first, then execute an INSERT query with the staging table as the source and the desired table as the destination. You could do a transform with an ActiveX script, but it'd likely be slower and a bigger PITA.

 

[[MS]]

This seems like a very very odd thing to do. Placing values from two distinct meanings into a single column. Can you explain why you would want to do this?

As for how to do it, I've never use dts, but it should only be a couple lines of code to do what you want. Look into creating a cursor, it allows you to move line by line through a table in a way that you choose, executing multiple statements for each line. So in your case you would create a new table, then use the cursor as a "for...each" loop, inserting two entries in the new table for each one in the original table.

 

[Cardboard Hammer]

This seems like a very very odd thing to do. Placing values from two distinct meanings into a single column. Can you explain why you would want to do this?

As for how to do it, I've never use dts, but it should only be a couple lines of code to do what you want. Look into creating a cursor, it allows you to move line by line through a table in a way that you choose, executing multiple statements for each line. So in your case you would create a new table, then use the cursor as a "for...each" loop, inserting two entries in the new table for each one in the original table.

If that's what he's trying to do, using a cursor is an extremely inefficient way to do it. Loading into a staging table and running two INSERT statements would be much more efficient.

 

[PTNL]

If that's what he's trying to do, using a cursor is an extremely inefficient way to do it. Loading into a staging table and running two INSERT statements would be much more efficient.

agreed.

 

[BGM]

hi, seemed not to recieve an email that were some replies.. nm

in the end i just used a staging table and using some insert statmeents as suggested, and that works fine

the reason i want to do this is that there are more fields in the table then the brief example i gave, and there is a 'dataType' field which references another table...

the spreadsheets people are using has the values entered all on the same row, but the database has them on seperate rows and identified by types, thats why

cheers lads!