I am a newbie at SQL Server, but I do have a little experience from
Coldfusion. Here's my Question.

We have a database for our business. We sell products. and we get price
lists in excel format all the time. I am working with DTS and I would like to
set it up, so that I can format the spreadsheet, properly and execute the
package. easy as 1,2,3 right?

Well I have tried many things from doing it in SQL DTS to Access, and
everything is a no go. I have limited SQL knowledge but here is of the things
I have tried and it seems to me it should work.

INSERT
INTO Product (ListPrice)
SELECT ListPrice
FROM
[Product$]
WHERE (ProductID = Product $ .ProductID)

The statement that you there will not work.

Is the data still in Excel or have you imported it?

You would need to use an ExecuteSQL task to do it the way you detail and
you will need to use either a Linked Server or OPENDATASOURCE to query
the Excel spreadsheet. Examples of both are in BOL.

You can alternatively use a DataPump to transfer the data easily enough
if the format is OK.

Allan

"Cavan Vannice" <Cavan Vannice (AT) discussions (DOT) microsoft.com> wrote in
message news:Cavan Vannice (AT) discussions (DOT) microsoft.com:

The data is still in excel, what is BOL?

"Allan Mitchell" wrote:

BOL is one of the single most useful resources for your SQL Server
installation. Books Online aka the help files.



"Cavan Vannice" <CavanVannice (AT) discussions (DOT) microsoft.com> wrote in
message news:CavanVannice (AT) discussions (DOT) microsoft.com:

I realised that after i posted, thanks

Here's where I am:

Latest query in DTS

INSERT INTO Product
(ListPrice)
SELECT [Product$].ListPrice
FROM Product INNER JOIN
[Product$] ON Product.ProductID = [Product$].ProductID


Syntax checks out and the it runs fine, but it does not do anything to the
database. To explain my self better heres the basics of what I am doing:

Product$(excel) Product

ProductID ------> ProductID
ListPrice ------> ListPrice
more
more
more
more
more
more

If the productID's match, then the price from the spreadsheet overwrites the
price in the database, and in the end the Manufactururs code will have to
match, right now I am simply working out the proccess.

For better understanding I am testing on AdventureWorks2000, I exported the
products table, modified the the top 5 lines, and am now trying to update the
prices by ProductID.

Thanks in advance for any help provided and already so.

Cavan



"Allan Mitchell" wrote:

In what task do you have this statement?

Product$ is in Excel still? If yes then how? Looks like a SQL Server
table after an import from Excel to me.

Allan


"Cavan Vannice" <CavanVannice (AT) discussions (DOT) microsoft.com> wrote in
message news:CavanVannice (AT) discussions (DOT) microsoft.com:

IT is a DTS Package Excel spreadsheet is a connection, then to the database,
then the execute SQL task. I am really not partial to any particular way of
doing it, just something that can be changed and implemented easy. This will
be a regular if not daily process.

Thanks

P.S. I wouldnt mind seeing some similar examples, if anyone knows any posts


"Allan Mitchell" wrote:

Ok So here is what you do

Add an Excel Connection.
Point it to your spreadsheet

Add a SQL Server connection
Point it to your SQL Server

Now create a transform data task coming from the Excel Spreadsheet to the
SQL Server

Double click on the Black line (The transform data task).

In the Source drop down the Table/View combobox and choose the Excel table
you require

Now go to the destinations tab and select your destination table

On the transformations tab map the source columns to thier respective
Destination.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Cavan Vannice" <CavanVannice (AT) discussions (DOT) microsoft.com> wrote