Generating fast test data in SQL Server
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]
Tags: Databases, SQL Server, Test Scripts