While working in the IT industry you acquire several tips and tricks for more efficient programming logic from your colleagues. This can happen during Peer Reviews, fixing bugs, or adding additional logic to code that was developed by a peer.  I have learned a lot from my colleagues, but one of the things that I have shared with my colleagues was something that I didn’t expect to teach. This is because to me it seemed like second nature. I am referring the code block that I use to insert multiple records into a database. This is becasue we all have different backgrounds and are taught a certain way of doing things, which may not always be the same way others do it.

When working with a database you often need to create scripts to insert hundreds or thousands or records into a database table. Although the logic is fairly simply, there are several ways it can be done.

For this exercise, we will create a table called EMPLOYEE with the following fields: EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeTitle, EmployeeHireDate), and I will give examples of 2 ways of inserting records into a database.

CREATE TABLE EMPLOYEE

(

EmployeeID int IDENTITY,
EmployeeFirstName varchar(50),
EmployeeLastName varchar(50),
EmployeeTitle varchar(100),
EmployeeHireDate datetime

)

Method 1: INSERT, SELECT, UNION ALL SELECT

The majority of the time I see programmers use the following INSERT, SELECT, UNION ALL SELECT logic to insert several records into a table. We will call this logic Method 1. Note that this is correct and acceptable logic to use. I have just always used an alternative method for inserting records into a database.

Here is what their code might look like:

SET IDENTITY_INSERT EMPLOYEE ON

INSERT INTO EMPLOYEE
(EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeTitle, EmployeeHireDate)
SELECT 1, ‘Annie’, ‘Adams’, ‘Human Resources’, ’01/15/2009′ UNION ALL
SELECT 2, ‘Brad’, ‘Bradley’, ‘Software Engineer’, ’05/25/2008′ UNION ALL
SELECT 3, ‘Caroline’, ‘Coates’, ‘Software Engineer’, ’12/25/2009′ UNION ALL
SELECT 4, ‘Deborah’, ‘Dunn’, ‘Human Resources’, ’05/30/2011′ UNION ALL
SELECT 5, ‘Ellie’, ‘Ellis’, ‘Project Manager’, ’06/11/2009′ UNION ALL
SELECT 6, ‘Harold’, ‘Henry’, ‘Project manager’, ’08/09/2000′ UNION ALL
SELECT 7, ‘Jessica’, ‘Jacobs’, ‘Quality Engineer’, ’08/05/2001′ UNION ALL
SELECT 8, ‘Katy’, ‘Keller’, ‘Graphic Designer’, ’08/25/2002′ UNION ALL
SELECT 9, ‘Lisa’, ‘Lovett’, ‘Software Engineer’, ’09/05/2009′ UNION ALL
SELECT 10, ‘Michael’, ‘McCormick’, ‘Software Engineer’, ’10/15/2010′

SET IDENTITY_INSERT EMPLOYEE OFF

 

Method 2: VALUES with Comma Separated Brackets

An alternative approach to the INSERT, SELECT, UNION ALL SELECT logic, is to just use comma separated brackets following the VALUES() command. We will call this Method 2. This is the approach I prefer and was surprised when a couple people didn’t think it was a possibility, and others prefer to use the INSERT, SELECT, UNION ALL SELECT method. Although my method, Method 2, accomplishes the exact same thing as the Method 1, it involves less code.

Here is what my code typically looks like:

SET IDENTITY_INSERT EMPLOYEE ON

INSERT INTO EMPLOYEE
(EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeTitle, EmployeeHireDate)
VALUES(1, ‘Annie’, ‘Adams’, ‘Human Resources’, ’01/15/2009′),
(2, ‘Brad’, ‘Bradley’, ‘Software Engineer’, ’05/25/2008′),
(3, ‘Caroline’, ‘Coates’, ‘Software Engineer’, ’12/25/2009′),
(4, ‘Deborah’, ‘Dunn’, ‘Human Resources’, ’05/30/2011′),
(5, ‘Ellie’, ‘Ellis’, ‘Project Manager’, ’06/11/2009′),
(6, ‘Harold’, ‘Henry’, ‘Project manager’, ’08/09/2000′),
(7, ‘Jessica’, ‘Jacobs’, ‘Quality Engineer’, ’08/05/2001′),
(8, ‘Katy’, ‘Keller’, ‘Graphic Designer’, ’08/25/2002′),
(9, ‘Lisa’, ‘Lovett’, ‘Software Engineer’, ’09/05/2009′),
(10, ‘Michael’, ‘McCormick’, ‘Software Engineer’, ’10/15/2010′)

SET IDENTITY_INSERT EMPLOYEE OFF
Whichever method you choose, it is great to have options. One method may work better than the other in certain cases. It’s up to you decide which method is easier for you, but it is great to know that you’ve got options!

Need to test the height of this box....

Social Wall