November 2, 2006 at 9:39 am
I have a table with three columns [partno](primarykey), [desc] and [price].
I already have data in [partno] and [desc] and have an excel spreadsheet which contains partno and price data.
I would like to update the table with the data from the excel spreadsheet, matching on partno.
How is this accomplished?
Thank you,
Ryan McAlister
November 2, 2006 at 5:20 pm
There are two ways that I can think of.
How is your VB skills? You could use Excels built-in VA programming languange to connect to your database and loop through the fields making updates as you go. Probably not the best option
OR
This one is better. You could save the Excel spreadsheet as a comma delimited file and BCP or bulk load it into a SQL Server temporary table, then write updates from that table.
Just look in Books On Line to help you with the syntax for loading the file, then Bob is your mothers fathers son.
November 3, 2006 at 5:51 am
There has got to be an easier way. I can do this in FileMaker by clicking a box on import.
November 3, 2006 at 6:34 am
I hope by FileMaker you don't mean that concoction created by IBM on their PS2 some years ago?
In any case, an alternative is to create a column in Excel that uses a formula to compose an sql update statement using the other columns. Then you transfer that column to a new sheet and save it in txt format. It then is effectively a script you can run.
November 3, 2006 at 11:22 am
You could import the data into a temp table and just do the update off of that.
November 3, 2006 at 11:26 am
How do I update from one table to another matching on PrimaryKey?
November 3, 2006 at 7:02 pm
update #temp2 set col3 =
(select col3 from #temp1
where #temp1.col1 = #temp2.col1)
go
Here is a complete scenario you can test in QA:
drop table #temp1
go
create table #temp1
(col1 char(2), col2 char(2), col3 char(2))
go
insert into #temp1 (col1, col2, col3) values ('01','02', '99')
go
drop table #temp2
go
create table #temp2
(col1 char(2), col2 char(2), col3 char(2))
go
insert into #temp2 (col1, col2) values ('01', '02')
go
select * from #temp1
select * from #temp2
update #temp2 set col3 =
(select col3 from #temp1
where #temp1.col1 = #temp2.col1)
go
select * from #temp1
select * from #temp2
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
November 3, 2006 at 11:30 pm
Careful with that update. It will set col3 to null when, for a record in #temp1, there is no record in #temp2. Adding a where clause will avoid this: where col1 in (select col1 from #temp2)
November 7, 2006 at 2:12 am
hi
u could use openrecordset or opendatasource functions to retrieve data from excel and update the table. I think you can use these functions in your update query itself.
for more info on these check BOL.
"Keep Trying"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply