Menu

SELECT HowTo FROM WriteSQLSyntax Part 1 : DML

In my years working in BPM, SQL syntax has played a part in almost every peice of work. The ‘long running’ characteristic of BPM requires that process and state data be persisted to a long term data store because this will ultimatley outlast the hosting server in terms of lenth of operation (some processes could go on for years). As well as using databases for peristing BPM state, almost every application that exists these days, including applications or systems that utilise BPM have a data store of some kind that will need to be queried for user or process data. It’s a given that you know SQL in this day and age.

For the experienced developer, this article is not for you.

SQL is a standard that allows for the general interaction with database data. We have the commands that manipulation existing data and the commands that build the structure of the data (tables, schemas etc). Microsoft have a flavour of standard SQL-92 that they name Transact-SQL and split this language into 2 main categories. DML (data manipulation language) are the statements used to manipulate your data using common statements such as SELECT and UPDATE. DDL (Data Definition Language) represents the TSQL statements that assist the management and maintenance of your database objects (ALTER PROCEDURE, DROP TABLE etc).

SQL skills are seen as a basic essential requirement these days in the fields of software development, BPM, EAI, web site design and even scripting and so having a this under your belt is a must. I’ve tried to create a ‘cheat sheet’ of sorts for the DML side of the TSQL language which can be used as a quick reference, starting with the basics :

SELECT (* means ALL columns)

SELECT * FROM POItems | SELECT POL_RowID, POL_OrderNo FROM POItems

DISTINCT(Unique values, non duplicates returned)

SELECT DISTINCT POL_InvApproverName FROM POItems

SELECT INTO (selects from one table and inserts into the other)

SELECT * INTO NewPOItems FROM POItems SELECT POL_OrderNo, POL_Description INTO NewPOItems FROM POItems