need logic for dynamic sql update statement

I would like to create a dynamic SQL update/insert statement based on some parsed data in a table in SQL and run this against an existing table in the same database. The issue I'm having is that some fields in the parsed datatable may not exist as fields in the copy-to table which will kill the statement. How can I check before hand?

i.e. parsed data table:
fieldname value
"address" "123 sample rd"
"customer" "bob's furniture"
"phone"     "12345678"

Dynamic SQL along the lines of:
sql += "update tblCopyTo set " 
for each row in parsed datatable
sql+= fieldname + "=" + value
end for

Possible problem:
tblCopyTo fields:
address
customer
customerphone

so "phone" from dynamic SQL does not exist in tblCopyTo and the script will fail and not update any other parameters. Since the parsed data is not static, I need to be able to remove the non-existant fields before running the update script.

Can someone help me with the basic logic?

thanks

acdagirlAsked:
Who is Participating?
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.

chapmandewCommented:
you could build the update statement based upon te fields returned by sys.columns for the table.  
0
acdagirlAuthor Commented:
ok, I'm working in .net though - how will I get the sys.columns values in the code? or I guess I should ask in .net forum?
0
acdagirlAuthor Commented:
actually i see what you are saying - get the sys.columns from a select statement and match them up. where can i get the sql select statement for the sys.columns of a table?
0
chapmandewCommented:
No, it is a database thing...just another step.  

--get the column names
SELECT name from sys.columns
where object_name(object_id) = 'tablename'

loop though this result setup and build your fields that youneed to update...that way you know exactly what fields are in the table based upon the results from sys.columns.  Then you can just work that into your previous query for getting the values.
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
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 2008

From novice to tech pro — start learning today.