Here is a quick and easy way to insert data into multiple tables joined by a foreign key constraint using only a SQL script.

I use this method often when I need to generate install scripts with static data or reusable and easily generated test data.

The method below uses the SQL Server @@identity variable to grab the last inserted id, assigning it to a temporary variable, and allowing it to be used in the second table to perform multiple row additions, linking each new row back to the parent record.

Without assigning it to a temporary variable as you will see below, the @@identity variable would reflect each new child row each time we did an insert, preventing us from linking the child rows back to our parent table.

DECLARE @fk_user INT;
DECLARE @fk_admin_role INT;
DECLARE @fk_user_role INT;

-- insert a user --
INSERT INTO users (username, password, firstname, lastname) VALUES('user_bill', 'password', 'billy', 'west');
SET @fk_user = @@identity;

-- insert some roles --
INSERT INTO roles (role_name) VALUES('user');
SET @fk_user_role = @@identity;

INSERT INTO roles (role_name) VALUES('admin');
SET @fk_admin_role = @@identity;

-- now populate the many to many table, with foreign keys from the stored values --
INSERT INTO users_roles (role_id, user_id) VALUES(@fk_user_role, @fk_user);
INSERT INTO users_roles(role_id, user_id) VALUES(@fk_admin_role, @fk_user);

The above now provides us with a script to quickly generate a basic multi role user, that we could use in a install script for our app or test script. We could now reload the data over and over again without needing to worry about specific primary keys and matching foreign keys, it is all done dynamically.

Each time we perform a insert we retrieve the last inserted primary key value and store it in a temporary variable. We then use these variables to combine and link our records via the “users_roles” table at the end once the required roles have been inserted.

[user] <– [users_roles] –>  [roles]

Not that its really programming related, but still web development related, I discovered this article today randomly which sparked my need to blog it.

http://www.iht.com/articles/2007/03/30/arts/design2.php

Font is one of the key things I pay attention too. I will spend a lot of time trying many fonts before I get something I am satisfied with, and usually in the end it is Helvetica. It has always been my favorite typeface, and because of this you will see it used in most of my web and logo design work. The logo used for this blog is actually a Helvetica Thin font.

Helvetica is so common amongst many areas of design and art that once you begin to use it heavily, you will also begin to notice it more in advertising, marketing material, even on your cereal box in the morning. Everywhere you go Helvetica is there, making it one of the most universal fonts in my opinion. It is very simple, yet very powerful and because of this is often used on its own with little graphical help. Minimalism design, presently a popular design trend on the web, makes heavy use of this simplicity/power relationship that Helvetica produces.

Below are some key points I have found beneficial when working with this font. I have also whipped up a quick example of the uses.

• In most cases it looks best with a very small letter spacing
• The bigger the better, especially when used with smaller complementary text
• Great for minimalism design

Now I suggest you load photoshop, select Helvetica and play.