Link to home
Start Free TrialLog in
Avatar of nanohurtz
nanohurtz

asked on

Using Excel to update values in a SQL Server 2005 table

I have a  table (dbo.tblPoint) that contain 2 columns (PiPointID and ServerName) that are populated with incorrect values. I have a spread sheet that contain the correct values for the 2221 records. Is there a VB snippet that can do the following.

1. Open up the spreadsheet (PIML.HCEROUND.08152007.DUPFIX.xls)
2. Read the 2 columns of values in the tab labeled "PIML SWAP04"
3. Make the correlation between the values in the TagName column from the spreadsheet with that of the column called "TagName" in the SQL database.
4. Update the PiPointID and ServerName column where the TagName Values are equal.

Version of SQL: 2005

Any help is greatly appreciated
Avatar of jj819430
jj819430

ok so basically you have two columns. Lets call them Col1 and Col2.
It sounds like Col1 is the Key you are matching on. If the Col2 isn't equal to what is in the DB, update.

to have the query for one ofthese, you will simply say

declare @TempVal varchar(1000) --or some value


--this for every row in the excel
select @TempVal = MyTableColumnToCompare from MyTable where MyKey = Col1
if @TempVal <> Col2
begin
update MyTable set MyTableColumnToCompare = Col2 where MyKey = Col1
end

so in excel in a third column just make a formula that concatenates the statements and pulls the values. Then copy and paste into Query analyzer
ASKER CERTIFIED SOLUTION
Avatar of raja_ind82
raja_ind82
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial