Category: sql

This is something I’ve been doing a lot lately.
Seem to find myself googling for the syntax every time I have to so this is a reference for me, mostly, but you can use it if you behave.

The T-SQL syntax for adding a calculated column to a table looks like this:

ALTER TABLE [<TableName>]
ADD <NewCalculatedColumnName> AS (<SQL for value>) PERSISTED

A couple of notes:

• <TableName> is the  name of the table you’re adding a column to.
• <NewCalculatedColumnName> is the name of the new column
• <SQL for value> is a valid T-SQL statement.
• PERSISTED – Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.

Occasionally I’ve had to update a database table with a value from another database table.

Now I’m not talking about updating a single value in a single row, that wouldn’t be hard with an update statement, we’re talking billions and trillions of rows… realistically though hundreds.

So we have two or more tables that we can join in a sql query.
Let’s say we have a tables TableA and TableB and that TableB has a foreign key referencing TableA.
A select query joining those two would look something like this:

SELECT
 A.ShippingDate,
 B.ShippingDate -- just so B won't be left out
FROM
 TableA A
 INNER JOIN TableB B
  ON A.Key = B.ForeignKey

That was easy. A simple inner join and we have all the columns we need.

An update through a join follows pretty much the same rules in the join department but replaces the SELECT with an UPDATE.
For the sake of the demo let’s now say TableA and TableB have the “same” column (as seen above) and that for every row in one table (TableA) we need to update the matching rows in the other (TableB).
For the sake of the demo let’s assume this value is a shipping date when an order is sent to a customer. We’ve set the value on TableA and now we need to mark all referenced rows in TableB with the same value, taken from TableA.

This is how it would be achieved:

UPDATE B
 SET B.ShippingDate = A.ShippingDate
FROM 
 TableA AS A
 INNER JOIN TableB AS B
  ON A.Key = B.ForeignKey

As you can see this SQL statement is using the same join as the first SQL statement, the only difference is the Update syntax on top.

So… like so many other problems this is not that tricky once you know how to do it.

And now you know 😉

Enjoy

One of the reasons why I started this blog was so I could gather my code stuff in one place. I have been using Google Docs (now Google Drive) for them but where’s the fun in that?
They are a lot easier to share using a blog, right?

So here goes one coding tip.

On some occasions I’m working on a new feature or change that needs some demo data. Some data that is as close to the current version of the live data as possible.
The difficult way of achieving that would be to script up the old table, create a new table using that script but with a different name, and then insert a lot of data into it through some, for sake of demonstration, ridiculously difficult process.

Or we could use SELECT INTO.
By using SELECT INTO we both create a table and populate it with data in one fell swoop.

-- We can select all the columns of a table into the new table:
SELECT *
INTO [DatabaseName].[SchemaName].[NewTableName]
FROM old_tablename;

As stated before what this does is create a new table with columns identical to the columns of the old table and populate the new table with all the data from the old table.

Neat huh?

The next one is too:

-- We can also select only the columns we want into the new table:
SELECT column_name(s)
INTO [DatabaseName].[SchemaName].[NewTableName]
FROM old_tablename

Same trick as before but now you’re selecting which columns to create in the new table along with copying the data that belongs to those columns.

I should warn you:
This script doesn’t copy over any indexes (indices) or triggers or any complimacated doowackies. Only data.
My database engine of choice is Microsoft’s SQL Server which you can get here for the nifty price of free (the express version that is).

I haven’t tested this script on any other database server and I don’t know if it’s ANSI compliant. 🙂

Fair enough?
Enjoy