Remove Duplicate Entry from Comma Delimited String

This is a very simple script which will remove duplicate entry from comma delimited string. User Defined Function has very simple logic behind it. It takes comma delimited string and then converts it to table and runs DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used.

I have modified original contribution from Ashish so now it completely covers the subject intended to cover. I would suggest that this UDF should be kept handy to perform this tedious task easily.

CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',')DistinctList
GO

I encourage my readers to send their contribution as well so I can include their contribution as well.

Difference Between Union vs. Union All

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Run following script in SQL Server Management Studio to see the result between UNION ALL and UNION. Download complete script from here.

/* Declare First Table */
DECLARE @Table1 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table1
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
/* Declare Second Table */
DECLARE @Table2 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table2
SELECT 'First'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fifth'
/* Check the data using SELECT */
SELECT *
FROM @Table1
SELECT *
FROM @Table2
/* UNION ALL */
SELECT *
FROM @Table1
UNION ALL
SELECT *
FROM @Table2
/* UNION */
SELECT *
FROM @Table1
UNION
SELECT *
FROM @Table2
GO

In our example we have two tables: @Table1 and @Table2.

Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.

We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.

Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.

Let me know what do you think about this article. If you have any suggestion for improvement please let me know and I will update articles according to that.

Reference : Pinal Dave

Cross tab query with PIVOT

Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.

PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:

ctrl_no id Amount OrderDate
1000029 100016 990 10/25/2012
1000029 100018 990 10/25/2012
1000029 100016 660 10/26/2012
1000029 100018 660 10/26/2012
1000029 206007 660 10/26/2012
1000029 206007 660 10/27/2012

This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:

id 10/25/2012 10/26/2012 10/27/2012
100016 990.00 660.00 NULL
100018 990.00 660.00 NULL
206007 NULL 660.00 660.00

Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.

CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT    id,
        [10/25/2012],[10/26/2012],[10/27/2012]
FROM (
    SELECT    id,
            Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN([10/25/2012],[10/26/2012],[10/27/2012])
) p
 DROP TABLE #TestTable

Reference: www.SQLYoga.com

Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:
I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List
Get Comma separated List

I want output like:

Get Comma separated List
Get Comma separated List

I come up with very good solution. Let me share with all of you:

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1
Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

PIVOT Command in SQL Server

There are many cool new features in SQL Server 2005. For example, “Extended Stored Procs“ written in .NET with connections via SqlContext. How cool is this!?!? Check out the new PIVOT command in T-SQL for SQL Server 2005…

declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, ‘Q1’, 70)
insert into @sales values(2001, ‘Q1’, 150)
insert into @sales values(2002, ‘Q1’, 20)
insert into @sales values(2001, ‘Q2’, 15)
insert into @sales values(2002, ‘Q2’, 25)
insert into @sales values(2001, ‘Q3’, 50)
insert into @sales values(2002, ‘Q3’, 20)
insert into @sales values(2001, ‘Q4’, 90)
insert into @sales values(2001, ‘Q4’, 80)
insert into @sales values(2002, ‘Q4’, 35)

select * from @sales
pivot
(
sum(Amount)
for Quarter
in (Q1, Q2, Q3, Q4)
as p

Year| Q1  |Q2  |Q3 |Q4
———–| ——— |——- |——- |——–
2001| 220 |15  |50 |170
2002| 20  |25  |20 |35

(2 row(s) affected)

Explanation of TRY…CATCH and ERROR Handling

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur. In SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

  • ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state number of the error.
  • ERROR_LINE: returns the line number where the error occurred.
  • ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'

Above code will return following result:

Error Detected
Command after TRY/CATCH blocks


If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.

Limitation of TRY…CATCH:

  • Compiled errors are not caught.
  • Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
  • Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.

For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) 

INNER JOIN With WHERE

Introduction

The purpose of this article is to show the difference between the On clause and Where clause when using with left join in SQL Server.

Background

I have been working on SQL Server databases for more than 5 years and I was not aware of the difference between the On clause and Where clause when used with left join. Once I asked this question to our DBA and he said that there is some difference but he was not be able to explain the difference. Then I started exploring this topic myself and got some interesting observations of these two clauses when used with left join. So today I decided to share my knowledge so that everyone could benefit from my work.

Using the code

Today I will explain the difference between the On clause and Where clause when used with left join in SQL Server. When the On clause is used in an outer join, the outer table will have no effect on this On clause and all rows from the outer table will be returned and the On clause determines which rows of the subordinate table joins to the outer table. Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output. It’s difficult to understand this from the above definition, so let’s try to understand this difference with an example. Suppose we have two tables Departments (deptId, deptName) and Employees (empID, DeptID, EmpName, Salary) and deptiD is the foreign key of the department table. An employee can have only one department where as a department can have many employees.

We have this sample data in the Department table:

Collapse | Copy Code

DeptId      DeptName

———– —————

1           HR

2           Payroll

3           Admin

4           Marketing

5           HR & Accounts

 

(5 rows)

And here is the sample data for the Employees table:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ———————

1           John            1           5000.00

2           Albert          1           4500.00

3           Crain           2           6000.00

4           Micheal         2           5000.00

5           David           NULL        34.00

6           Kelly           NULL        457.00

7           Rudy            1           879.00

8           Smith           2           7878.00

9           Karsen          5           878.00

10          Stringer        5           345.00

11          Cheryl          NULL        NULL

 

(11 rows)

Case 1

Suppose we are asked to show all the employees and their relevant departments, then we would write a query like this:

Collapse | Copy Code

select * from employees e

<p>left join departments d on e.deptid = d.deptid</p>

 

(query1)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

3           Crain           2           6000.00               2           Payroll

4           Micheal         2           5000.00               2           Payroll

5           David           NULL        34.00                 NULL        NULL

6           Kelly           NULL        457.00                NULL        NULL

7           Rudy            1           879.00                1           HR

8           Smith           2           7878.00               2           Payroll

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

11          Cheryl          NULL        NULL                  NULL        NULL

 

(11 rows)

(Table 1)

Here you can see that all rows from the Employees table are listed along with their department, if they have a department; otherwise, deptid and deptname are null.

Case 2

Let’s say we are asked to show the list of all employees and the department name of only those employees who have “HR” or “HR & Accounts” department; then we would write a query like this:

Collapse | Copy Code

select * from employees e

left join departments d on e.deptid = d.deptid

and ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 2)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

3           Crain           2           6000.00               NULL        NULL

4           Micheal         2           5000.00               NULL        NULL

5           David           NULL        34.00                 NULL        NULL

6           Kelly           NULL        457.00                NULL        NULL

7           Rudy            1           879.00                1           HR

8           Smith           2           7878.00               NULL        NULL

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

11          Cheryl          NULL        NULL                  NULL        NULL

 

(11 rows)

Table (2)

You can notice here that only the same number of rows are returned as in query 1, but here the department of only those employees are listed whose department name is “HR” or “HR & Accounts”. As you can see, “Crain”,” Micheal”, and “Smith” have department “Payroll” (see table 1), but it’s not listed in the above result set because here we applied the filter in the “On clause”; hence only those rows become part of the join that has department name “HR” or “HR & Employee”, and as a result, all other employees who don’t have department name “HR” and “HR & Accoutns” show their department name as null. This is an example of the On clause in left join.

Case 3

What if we are asked to show only those employees who have their department name “HR” or “HR & Accounts”? We would write a query like this:

Collapse | Copy Code

select * from employees e

left join departments d on e.deptid = d.deptid

where ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 3)

and the result of this query is:

Collapse | Copy Code

EmpId       EmpName         DeptId      EmpSalary             DeptId      DeptName

———– ————— ———– ——————— ———– —————

1           John            1           5000.00               1           HR

2           Albert          1           4500.00               1           HR

7           Rudy            1           879.00                1           HR

9           Karsen          5           878.00                5           HR & Accounts

10          Stringer        5           345.00                5           HR & Accounts

 

(5 rows)

Table (3)

You can see here that only those employees are listed who have their department “HR” or “HR & Accounts”, so what we did here is the same query as query 1. You can check the result of query 1 in table 1, and then we just applied a filter in the Where clause to filter the rows and to return only those employees who have their department name as “HR” or “HR & Accounts”. This is an example of Where clause in left join.

Now you have seen all three cases and can see how these differ from each other. This was an example of a (1:1) one to one table as one employee can have only one department. Let’s execute the above 3 cases with (1:M), a one to many table relationship where one department can have multiple employees (or more than one employee can be in one department).

Case 4

Let’s suppose our requirement is to get all departments and the employees listed in these departments, then we would write a query like:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId

(query 4)

It will return all rows from the Department table and only those rows from the Employees table that have a department. The result will look like:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

2           Payroll         3           Crain           2           6000.00

2           Payroll         4           Micheal         2           5000.00

2           Payroll         8           Smith           2           7878.00

3           Admin           NULL        NULL            NULL        NULL

4           Marketing       NULL        NULL            NULL        NULL

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

(10 rows)

(Table 4)

You can see in the result that all departments are listed, even those that don’t have any employees, as “Admin” and “Marketing”.

Case 5

But what if we are asked to show all departments and only those employees who have department name “HR or “HR & Accounting”. Then we would write a query like:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId and

( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 5)

It will show the following records:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

2           Payroll         NULL        NULL            NULL        NULL

3           Admin           NULL        NULL            NULL        NULL

4           Marketing       NULL        NULL            NULL        NULL

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

 

(8 rows)

 

Table (5)

You can see in the result, this is the On clause in left join; it just return all rows from the Department table and only those rows from the Employee table that have department “HR” or “HR & Accounts”. We have employees in the Payroll department (Table 4 as a result of query 4) but it’s not listed in the above result set as we just put an extra condition in the “On clause” that the department name should be “HR” or “HR & Accounts”. That’s why only those rows were part of this join which have department name “HR” and “HR & Accounts”. Hence all rows from the Department table were returned and only those matching rows were returned from the Employees table who have their department as “HR” or “HR & Accounts”.

Case 6

What if we are asked to show only the “HR” and “HR & Accounts” departments along with their relevant employees? We would then write a query like this:

Collapse | Copy Code

select * from departments d

left join employees e on e.deptId = d.deptId

where ( d.deptname = ‘HR’ or d.deptname  = ‘HR & Accounts’)

(query 6)

and the result of the above query would be:

Collapse | Copy Code

DeptId      DeptName        EmpId       EmpName         DeptId      EmpSalary

———– ————— ———– ————— ———– ———————

1           HR              1           John            1           5000.00

1           HR              2           Albert          1           4500.00

1           HR              7           Rudy            1           879.00

5           HR & Accounts   9           Karsen          5           878.00

5           HR & Accounts   10          Stringer        5           345.00

 

(5 rows)

 

(Table 6)

So what happened here? We just did a left join as we did in query 4, and you can see the result in table 4. And then we apply a filter on the result set to return only those rows that have deptNmae as “HR” or “HR&Accounts”. This is a Where clause in left join. Now you can see how it is different from the result of query 5 where we put this condition in the “On clause”.

There is no difference between the Where clause and On clause when used with inner join.

Reference : CodeProject.com

SQL Server – transactions roll back on error

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO myTable (myColumns …) VALUES (myValues …);
INSERT INTO myTable (myColumns …) VALUES (myValues …);
INSERT INTO myTable (myColumns …) VALUES (myValues …);

COMMIT TRAN — Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN –RollBack in case of Error

— you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH

Multi-table INSERT using one SQL statement in AIR SQLite

This article describes a way that you can use a single INSERTstatement to add data to multiple tables in the SQL dialect supported by the SQLite engine in Adobe AIR.

Normally in SQL, including in AIR’s built-in SQLite database engine, you can only add data to one table at a time using anINSERT statement. In some cases, particularly when you’re doing a “bulk loading” operation such as importing data from a text file into a database, it’s convenient to be able to insert data into multiple tables using a single INSERT statement.

For example, suppose you have XML data to import into a database such as the following:

<?xml version="1.0"?>
<employees>
    <employee firstName="Bob" lastName="Smith"
        location="San Francisco" country="USA"/>
    <employee firstName="Harold" lastName="Jones"
        location="San Francisco" country="USA"/>
    <employee firstName="Tom" lastName="Donovan"
        location="Boston" country="USA"/>
    <employee firstName="Mike" lastName="Wilson"
        location="Calgary" country="Canada"/>
    <employee firstName="Steve" lastName="Thomas"
        location="London" country="UK"/>
    <employee firstName="Joe" lastName="Nelson"
        location="London" country="UK"/>
    <employee firstName="Juan" lastName="Varga"
        location="Buenos Aires" country="Argentina"/>
    ...
</employees>

The XML data isn’t normalized, so there is duplicate data between the various records. We will import it into a database with the following (normalized) structure:

Data model for the database

Assuming the data is going to be imported as a single user operation, it would be painful to need to prompt the user or throw errors for every duplicate entry.

Using the technique described here, you can use a single SQL statement to add an employee and if necessary any related data including office location and country. (You would still loop over the data and execute one INSERT statement per employee record — but you wouldn’t need to execute three INSERTstatements per employee record, or need to check for duplicate office locations and countries for each employee record to be inserted.)

As mentioned above, this technique is probably only appropriate when you’re doing “bulk importing” of data. If you’re just adding a single conceptual record (even if it includes data in multiple tables) you’ll most likely want to use a series of INSERT statements to add the data, so that you can have more precise error checking and handling.

How to do it

In an attempt to “cut to the chase” I’m going to put the necessary code here. For more detailed explanations on how this works and why it’s necessary, see the “details” section below.

This technique for inserting data into multiple tables using a single SQL statement consists of three elements:

  1. A view in the database that groups the data to be inserted (from all the tables) into one “table”
  2. An INSERT statement to add the data, using the view as the destination “table” in the INSERT statement. This is theINSERT statement that you’ll run from your application while importing the data
  3. A trigger defined on the view, which runs when any INSERTstatement is executed against the view. This trigger does the actual work of checking for existing data and inserting data into individual tables.

Here’s the code for each part:

A view grouping the data to insert

This combines all the tables that potentially need data inserted into a single “table” for the INSERT statement. You run this statement once to create the view in the database.

CREATE VIEW employees_for_insert AS
SELECT e.firstName,
    e.lastName,
    l.name AS locationName,
    c.name AS countryName
FROM employees e
    INNER JOIN locations l ON e.locationId = l.locationId
    INNER JOIN countries c ON l.countryId = c.countryId

An INSERT statement that “inserts” the data into the view

This code isn’t actually run, but it passes all the data to the database engine for use in the trigger. You execute this SQL statement once for each record to add to the tables.

INSERT INTO employees_for_insert
(
    firstName,
    lastName,
    locationName,
    countryName
)
VALUES
(
    :firstName,
    :lastName,
    :locationName,
    :countryName
)

An INSTEAD OF trigger defined on the view

This is the code that actually runs when the INSERT statement above is executed. You run this code once to create the trigger. Then the database runs the code in the trigger body automatically. If a location or country doesn’t exist it is inserted. However, if they do exist, nothing happens (no duplicate record is created). Then the main employee record is inserted.

CREATE TRIGGER insert_employees_locations_countries
INSTEAD OF INSERT
ON employees_for_insert

BEGIN

INSERT INTO countries (name)
SELECT NEW.countryName
WHERE NOT EXISTS
    (SELECT 1 FROM countries
     WHERE name = NEW.countryName);

INSERT INTO locations (name, countryId)
SELECT NEW.locationName, countries.countryId
FROM countries
WHERE countries.name = NEW.countryName
AND NOT EXISTS
    (SELECT locationId
     FROM locations
     WHERE name = NEW.locationName);

INSERT INTO employees (firstName, lastName, locationId)
SELECT NEW.firstName,
    NEW.lastName,
    locations.locationId
FROM locations
WHERE locations.name = NEW.locationName;

END

Details

The SQL language is designed for working with relational databases, so in a SQL database you usually use multiple tables to represent a single piece of data. That means that in a normal scenario, if you want to add a new record to a table, you may need to add a new row of data to additional tables that the main data is related to.

In this example, we are using a database with the following structure:

Data model for the database

This data represents employees in a large company that has multiple office locations, identified by records in the locationstable. In fact, this company is an international company that has offices in different countries, including multiple offices in some countries (represented by the countries table.

Each employee is associated with their primary office location by the locationId field in the employees table, and each location is defined as being in a certain country by thecountryId column in the locations table.

In order to add a new employee you would generally have to perform several steps:

  1. Check whether the country where the employee’s office is located exists in the countries table
  2. If not, add it; if so, get its id to create the relationship with the locations table
  3. Make sure the office location exists in the locations table
  4. If necessary add the location record, and get its id to use in the employee record
  5. Add the employee record to the employees table

This is a fairly complicated process because a single SQLINSERT statement can only operate on one table at a time. In a simple case where you are adding a single employee record, this complexity isn’t unbearably difficult. You can execute the series of SQL statements in sequence in a single transaction. If an error occurs, you can break out of the sequence and display an error message or handle the error as desired.

However, if you’re importing a large set of data it’s not convenient to throw errors or display dialogs to the user. Instead, it would be nice to be able to just add any dependent data where appropriate, and insert all the data in one step.

The technique that’s described in this article makes use of database views and a SQLite feature known as INSTEAD OFtriggers.

view is a predefined SELECT statement that’s saved in a database so it can be used in queries as though it was a table. Because it usually includes data from multiple tables, and doesn’t necessarily include all the data from any given table, a view is generally read-only — you can use a SELECT statement to retrieve data from a view, but you can’t use an INSERT,UPDATE, or DELETE statement to modify the view data.

However, in SQLite (and consequently in AIR) you can define a special type of trigger that’s associated with a view known as an INSTEAD OF trigger. (A trigger is a set of SQL code that’s associated with a table. The code is executed when a data manipulation operation is performed on that table.) When a SQL statement attempts to perform an INSERTDELETE, orUPDATE operation on the view that has an INSTEAD OF trigger defined for that particular operation, the trigger is executedinstead of the specified operation. You can only define INSTEAD OF triggers on views (it wouldn’t make sense to use one for a table). For more information about triggers, see the Adobe AIR SQL reference documentation for the CREATE TRIGGERstatement.

In this example, a view is defined that includes all the data in all the tables that potentially need data inserted. When theINSERT statement is executed the database runs the trigger instead. The trigger contains code that checks whether related data in the locations and countries tables already exists, and adds it if necessary. Then it adds the employee record with the related key values.

SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1

HH:MIAM (or PM)

Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +

milliseconds

SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical

(with milliseconds)

SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM
Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]

08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 20061
Mon YYYY1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]

12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]

92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY] Sep-02 1
Mon-YYYY1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY] 25-Dec-20051

1 To make the month name in upper case, simply use the UPPER string function.