I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.I would bcp the data into a staging table, then use sql to do whatever you have to do.
But that's just me
MOO|||I'm not sure what bcp stands for ... I've only seen dts. I think your idea of using a staging table to query data from sounds good, but I kind of want this to be as simple a process as possible. Something that relatively anyone with instructions could click buttons and have done routinely.|||use dts to bring the excel data into a staging table
(a table created just to store data in the middle of a coomplex import)
then use an update statement against the staged data to update the destination data
(use the transact sql task)
For a more straightforward (but more advanced) method you might consider using the data driven query task and you could eliminate the staging table.
once you get this running you can set it up as a batch file or script or even call it from a sp. so anyone can run it.|||I've called tech support and checked permissions on the product table, but niether of us can seem to get this query to work when importing data from an excel workbook, Peach$. Is there something wrong with my query?
Here's what the support said when he tried:
It doesnt appear to be working running as SA either. I get a Error Line 3 near ) and then the db is read-only.
I cleared active connections to the DB and even stopped access, but it still appears to be locked by something, or perhaps the table is protected somehow?
--Raj
Here is the actual query:
update ASI.dbo.Product
set ASI.dbo.Product.In_Stock = (select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`)
where exists
(select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`);
Sorry for the lag time with a reply. I hope this gets read now.
MarkWest05 wrote: