Program With Dave

Sometimes it can be confusing what the difference between ‘union’ in a SQL statement and ‘union all’ means. The basic definition is that ‘union’ will combine a given set (query results) with the values of a second set that don’t already exist. ‘Union All’ will simply append the second set to the end of the first set, which includes all the records of the first set and all the records of the second set.

Let’s see an example.

1
2
3
4
5

SELECT 1 MyValue
 
UNION
 
SELECT 1 MyValue

Here we are combining two sets of data which each contain a single record, 1. If you execute this command, you will get back a single row which contains the value 1 in the MyValue column. This is because the new value we union in already existed in the first data set, so it did not include it.

Let’s modify our query to use Union All.

1
2
3
4
5

SELECT 1 MyValue
 
UNION ALL
 
SELECT 1 MyValue

We now get back a set with two records, the 1 from the first set AND the 1 from the second set.

Given a table MyTable, with the rows ID int, Value1 varchar(100), Value2 varchar(100) and a data context (created automatically by visual studio) you can create/update/delete data very easily. I will be using LINQ To SQL here, which is somewhat out of date now with the introduction of the Entity Framework, but I will post up some EF examples in a later post (they’re not much different in the code.) The language is C#.

Simple L2S Examples

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

using (DBDataContext db = new DBDataContext())
{
	// get all
	var search = from t in dc.MyTable
		           select t;
 
        // this will return all the records
	List<MyTable> all = search.ToList();
 
	// get all (example 2) This one doesn't use a search variable.
	List<MyTable> all2 = dc.MyTable.ToList();
 
	// get single
	var search2 = from t in dc.MyTable
		              where t.ID == 1
			      select t;
 
        // if none is found, then record will be NULL.
	MyTable record = search2.SingleOrDefault();
 
	// get single (example 2) This one uses a predicate which defines
        // 's' as a placeholder, and then sets the conditions for s to be s.ID == 1.
	MyTable record2 = dc.MyTable.SingleOrDefault(s => s.ID == 1);
 
        // update a value in the record
        record2.Value1 = "Hello!";
        db.SubmitChanges();
 
        // delete the record
        db.MyTable.DeleteOnSubmit(record2);
        db.SubmitChanges();
}

Sometimes its necessary to populate an existing table with the results of a query. This can be done very easily with an INSERT statement combined with a SELECT.

A typical insert query looks like this.

1
2
3
4
5
6
7
8
9
10

INSERT INTO Person 
     (Name, 
      Address, 
      Phone
     )
VALUES 
     ('Test Guy', 
      '123 Test St', 
      '555-5555'
     )

This works fine for just one row. But what if we had another table which contained the information we needed, and had 100000 rows that we wanted to copy over? Let’s just combine our INSERT with a SELECT!

1
2
3
4
5
6
7
8
9
10
11

INSERT INTO Person 
     (Name, 
      Address, 
      Phone
     )
SELECT
     Name,
     Address,
     Phone
FROM
     TableThatHasValues

It’s that easy! The SELECT query will execute and the results of that select query will be piped into the INSERT command, inserting every single row.

If you have a series of data that you want to insert into a new table, or into a temporary table, it’s very simple. This can be used to create a simple backup of records, or to take a very long and complex query and save its results for future use. If you save into a table, that table won’t have any relationships that are in the existing table, just something to keep in mind.

Let’s say we have a query like this.

1
2
3
4
5
6

SELECT
     *
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

This table will join together two tables and pull in the details of whatever animals are in the animals table. I just made this up quickly. Let’s have SQL save that to a new table.

1
2
3
4
5
6
7
8

SELECT
     *
INTO 
     AnimalDetailsCombined
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

This query will create a brand new table called AnimalDetailsCombined and save the results of that query into the table. If you wanted to save to a temporary table, which would be deleted once the connection is closed, just add a hashtag (#) in front of the table name.

1
2
3
4
5
6
7
8

SELECT
     *
INTO 
     #AnimalDetailsCombined
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

You now have a temporary table called #AnimalDetailsCombined with is the result of the query.