Re: Newbie: Update From Excel

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 spread*** 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:

> BOL is one of the single most useful resources for your SQL Server
> installation. Books Online aka the help files.
>
>
>
> "Cavan Vannice" <CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:CavanVannice@xxxxxxxxxxxxxxxxxxxxxxxxx:
>
> > The data is still in excel, what is BOL?
> >
> > "Allan Mitchell" wrote:
> >
> > > 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 spread***. 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > > message news:Cavan Vannice@xxxxxxxxxxxxxxxxxxxxxxxxx:
> > >
> > > > 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 spread***, 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)
> > >
> > >
>
>
.

Loading