| Groups | Blog | Home |
sql server dts : Newbie: Update From Excel
installation. Books Online aka the help files. "Cavan Vannice" <CavanVannice@discussions.microsoft.com> wrote in message news:CavanVannice@discussions.microsoft.com: [quoted text, click to view] > 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 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@discussions.microsoft.com> wrote in > > message news:Cavan Vannice@discussions.microsoft.com: > > > > > 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) > > > >
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@discussions.microsoft.com> wrote in message news:CavanVannice@discussions.microsoft.com: [quoted text, click to view] > 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: > > > BOL is one of the single most useful resources for your SQL Server > > installation. Books Online aka the help files. > > > > > > > > "Cavan Vannice" <CavanVannice@discussions.microsoft.com> wrote in > > message news:CavanVannice@discussions.microsoft.com: > > > > > 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 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@discussions.microsoft.com> wrote in > > > > message news:Cavan Vannice@discussions.microsoft.com: > > > > > > > > > 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@discussions.microsoft.com> wrote in message news:Cavan Vannice@discussions.microsoft.com: [quoted text, click to view] > 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)
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 data is still in excel, what is BOL?
[quoted text, click to view] "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 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@discussions.microsoft.com> wrote in > message news:Cavan Vannice@discussions.microsoft.com: > > > 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) >
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 [quoted text, click to view] "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@discussions.microsoft.com> wrote in > message news:CavanVannice@discussions.microsoft.com: > > > 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 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@discussions.microsoft.com> wrote in > > > message news:Cavan Vannice@discussions.microsoft.com: > > > > > > > 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) > > > > > > >
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 [quoted text, click to view] "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@discussions.microsoft.com> wrote in > message news:CavanVannice@discussions.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: > > > > > BOL is one of the single most useful resources for your SQL Server > > > installation. Books Online aka the help files. > > > > > > > > > > > > "Cavan Vannice" <CavanVannice@discussions.microsoft.com> wrote in > > > message news:CavanVannice@discussions.microsoft.com: > > > > > > > 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 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@discussions.microsoft.com> wrote in > > > > > message news:Cavan Vannice@discussions.microsoft.com: > > > > > > > > > > > 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) > > > > > > > > > > > > > > > > >
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 [quoted text, click to view] "Cavan Vannice" <CavanVannice@discussions.microsoft.com> wrote in message news:06D15DA7-0998-482A-AD5D-4D2B3AADA566@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: > >> 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@discussions.microsoft.com> wrote in >> message news:CavanVannice@discussions.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: >> > >> > > BOL is one of the single most useful resources for your SQL Server >> > > installation. Books Online aka the help files. >> > > >> > > >> > > >> > > "Cavan Vannice" <CavanVannice@discussions.microsoft.com> wrote in >> > > message news:CavanVannice@discussions.microsoft.com: >> > > >> > > > 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 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@discussions.microsoft.com> wrote >> > > > > in >> > > > > message news:Cavan Vannice@discussions.microsoft.com: >> > > > > >> > > > > > 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) >> > > > > >> > > > > >> > > >> > > >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |