Google Analytics

Search This Blog

Friday, October 18, 2013

SQL Friendly Database Insert and Update Process

Disclaimer: This article if read without prior experience may end up boring and dry.

T-SQL Insert and Update are part of the Data Manipulation Language (DML).

http://technet.microsoft.com/en-us/library/ff848766.aspx

The biggest bane with T-SQL DML belongs to an issue so common that 100% of programmers will encounter it in the first place - the reason being is that we had expected the T-SQL to be more intelligent friendly, but it didn't.

As the matter of fact, T-SQL is not that friendly and it requires one to follow specific rules.

The biggest bane is to perform DML with string data that consist of single quotes; it will result in exception because under T-SQL, single quote is considered to be a control character or reserved character. Hence, when a string data consists of single quote such as Brandon's PC, it will create confusion.

The best practice solution is to perform what is known as single quote offset.

i.e

Suppose I want to perform INSERT into a table called EmployeeTBL

INSERT INTO EmployeeTBL (Name,Position) VALUEs ('Brandon', 'General Manager')


When single quote is required to be present as part of the data, it will go like this supposedly.

INSERT INTO EmployeeTBL (Name,Position) VALUEs ('Michelle', 'Brandon's PA')


Nevertheless, the above T-SQL statement will generate exception; 'Incorrect syntax near ...'

The solution to perform single quote offset by adding an additional single quote in front of the single quote which belongs to the string data, hence:

INSERT INTO EmployeeTBL (Name,Position) VALUEs ('Michelle', 'Brandon''s PA')


Notice that now Brandon''s PA has two single quotes - this is what we mean by offsetting, it will work well for T-SQL.

Today, I just want to go further to discuss some strategies to deal with this issue on a broader perspective.

Continue reading.

No comments: