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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chapmandewConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.