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.DU PFIX.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
1. Open up the spreadsheet (PIML.HCEROUND.08152007.DU
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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