MySQL Style INSERT INTO SET

MySQL allowed INSERT statements that looked like this:

INSERT INTO Table SET col1 = value1, col2 = value2, col3 = value3

Basically this closely matched the format of the UPDATE statements.

Here is why this was good: I could do something like this:

if (RecordExists ==false) { SQLquery ="INSERT INTO Table SET ";}else { SQLquery ="UPDATE Table SET ";}SQLquery +="col1 = value1, ";SQLquery +="col2 = value2, ";SQLquery +="col3 = value3, ";SQLquery +="col4 = value4 ";if (RecordExists ==true) { SQLquery +="WHERE id = " + ourID;}

If I had a good 50 columns then it means I can reuse a sizable chunk of code for both INSERT and UPDATE statements (since both use the same general format). Whereas if I have to use "INSERT INTO Table(columns) VALUES (values)" then I'm look at duplicating a lot of code.

Does MS-SQL support something similar to "INSERT INTO SET"? How are others dealing with this?

Thanks,

Gabe
-------

No. That is a proprietary MySQL syntax.

How do I deal with it? I don't. I don't use SQL concatenation techniques. They make the application/web apps insecure, difficult to port, slow, hard to maintain, and locks the application developer into doing database design (Which isn't good for team development). Not only that but it limits what you can do from the database design, and in multi-application environments where you have many applications using the same data tables, difficult to coordinate since the data-logic has now been moved to each application.

And lastly, for some applications it's simply isn't even an option. For example in many HIPAA applications, since you are allowing applications to retrieve data without the ability to forcably log the request, it's not even an option to do it that way.

|||

Motley, you answered my question and for that I thank you.

The code I was showing was simplified for forum purposes. (I just wanted to demonstrate the overall logic.) In a real situation I would make use of the AddParameter method. But this probably still qualifies as using SQL concactenation and in-line SQL, which I know many consider a sin.

In my experience application developers are almost always are up to their eye-balls in database design anyway...even when working with a DBA. I know I always am. Many database search queries I deal with are tied closely to application conditions and have a lot of processing logic involved in them. I could do that processing logic in C# (which is extremely feature rich, has great development tools available & benefits from access to my application objects) or I could do it in a SQL stored procedure which seems generally clunky & not nearly as robust. Using lots of stored procedures normally just means I have one more place I have to update something or one more person I have to involve for a fix.

I'm not going to pretend to be an expert on this and some of this just comes down to getting the job done. Many of the security advantages you speak about could probably be mimiced by simply having well defined user roles. From what I've read the performance advantages aren't as overwhelming as people seem to believe. I have read that many of the performance enhancements MS-SQL makes to queries are extended to in-line queries as well.

From an application standpoint having a well developed set of database interaction objects becomes a dream. I know a ton of people are going to disagree with me and I may even change my mind on this subject someday. For me personally, most of the stuff you speak of has just proven to be a giant PITA.

Gabe
============

|||What is a PITA?|||And why SQL concatenation techniques make the application/web apps insecure??|||

And why SQL concatenation techniques make the application/web apps insecure??

I would like to use the same style.

|||

Hey

SQL concatenation techniques will result ini sql injection.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

You could take a look atSQL Injection for details.