How to update a field using an excel file

I would like to update a field in my system with a value from an excel file
The excel file is called Hull Equip and it contains two columns Equipment No (which contains the existing values) and New Equip No (which contains the new number)
The table I am trying to update is called EQUIP and the field is called EQNUM
How would the query look?
Gordon
Gordon HughesDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
Move first column after the second one, add two extra columns: one before the first column and one between two columns,
put into the first cell text:
update tableName set columnName = '
propogate it all rows
put into the first row third column text:
'' where columnName = '
propogate it all rows
put into the first row fifth column text:
';
propogate it all rows

copy all content to notpad for insance and remove all Tabs by command replace Tabs with empty string
Your SQL script is ready to execute.

Don't forget to replace tableName & columnName with your real identifiers.

If you need to execute such procedure automatically, you could record macro and play it in Excel
0
Gordon HughesDirectorAuthor Commented:
Hi
That sould complicated to me
What if i import the excel into a new table in the database and then run a sql script
Not sure what the script would look like
Gordon
0
OMC2000Commented:
In fact it was the simplest way.
This article discribes how to import excel into a new table in sql server.
https://support.microsoft.com/en-us/help/321686/how-to-import-data-from-excel-to-sql-server

Then you could execute the following command to update your data in original SQL server table with data from imported Excel table

update EQUIP set EQNUM = (select "New Equip No" from XLImport9 where XLImport9."Equipment No"=EQUIP.EQNUM )
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gordon HughesDirectorAuthor Commented:
Hi
Well not quite there
I have imported the excel file into(in the import I changed the values to varchar etc
and called the table HullEQ
I found the the EQNUM is used in several tables and view
So created the following
Update TASKEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = TASKEQ.EQNUM)
Update WOC
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOC.EQNUM)
Update WOE
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOE.EQNUM)
Update WOP
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOP.EQNUM)
Update WOV
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOV.EQNUM)
Update WOCHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOCHVW.EQNUM)
Update WOCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOCVW.EQNUM)
Update WODESCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WODESCVW.EQNUM)
Update WOEHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEHVW.EQNUM)
Update WOEQCVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQCVW.EQNUM)
Update WOEQLCOM
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLCOM.EQNUM)
Update WOEQLHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLHVW.EQNUM)
Update WOEQLVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQLVW.EQNUM)
Update WOEQOVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEQOVW.EQNUM)
Update WOEVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOEVW.EQNUM)
Update WOHISTEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOHISTEQ.EQNUM)
Update WOPHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOPHVW.EQNUM)
Update WORHEQVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WORHEQVW.EQNUM)
Update WOVHVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = WOVHVW.EQNUM)
Update XWOEQVW
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = XWOEQVW.EQNUM)
update EQUIP
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = EQUIP.EQNUM)

But for some reason the underlined red elements (NewEQNUM and HullEQ) says invalid column name or invalid object

Any ideas
Gordon
0
OMC2000Commented:
Are you sure you imported Excel data to the same database and schema?
What is the result of the following query:

select * from HullEQ

?
0
Gordon HughesDirectorAuthor Commented:
Well it is very strange
The table is shown in the correct database
run Select * from HullEQ (if i hover over HullEQ iy says Invalid object name)
when i press execute it returns all the data under headings EQNUM and NewEQNUM

Gordon
0
Gordon HughesDirectorAuthor Commented:
OK I restarted the SQL server and now it recognised the table
Now when I run the query is come back against every table

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EQNUM', table 'MP2Live.dbo.TASKEQ'; column does not allow nulls. UPDATE fails.

The HullEQ table does not contain any Null values as far as I can determine

Gordon
0
OMC2000Commented:
well, for tables where this columns is not null we should add extra condition:

Update TASKEQ
Set EQNUM = (select NewEQNUM from HullEQ where HullEQ.EQNUM = TASKEQ.EQNUM)
where TASKEQ.EQNUM in (select EQNUM from HullEQ)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gordon HughesDirectorAuthor Commented:
OK have changed all the lines
Now I have to deal with duplicates before it will work, so will close this question
0
OMC2000Commented:
The original question was answered. Alternative solutions were proporsed. Tipical problem was resolved. There is no real problem with the final solution. So, objection
0
Gordon HughesDirectorAuthor Commented:
Hi
What is this all about?
0
Gordon HughesDirectorAuthor Commented:
All good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.