Inserting Multiple Rows before SQL Server 2008

Posted: 12 Şubat 2013 in SQL, SQL SERVER

Prior to SQL Server 2008 it is not possible to provide multiple sets of raw data to an INSERT statement to create more than one row in a table. However, it is possible to insert a query’s results into a table. This can be used to add several rows at once.

UNION ALL Trick

In the last published article I described how you can use the updated syntax of the INSERT statement in SQL Server 2008 to insert multiple rows of raw data using a single command. I mentioned that in order to achieve the same results in SQL Server 2005 or earlier editions you needed to use the “UNION ALL trick”. Several readers asked me to explain this, so this is the topic of this article.

As mentioned previously, SQL Server 2008 allows more than one row of data to be included in an INSERT statement, giving possibilities such as the following:

INSERT INTO Salespeople
    (Name, Area, SalesTarget)
VALUES
    ('Bob', 'East', 100000),
    ('Jim', 'West', 120000),
    ('Mel', 'North', 110000),
    ('Sue', 'Central', 120000)

The above command inserts four new rows into the Salespeople table. If you try to execute it in SQL Server 2005 or an earlier version, you will receive an error. It is possible to insert multiple rows at once in earlier editions but only if the information being added is produced using a query and inserted using the INSERT INTO SELECT version of the INSERT statement.

If you want to insert multiple rows of raw data, you simply need to convert those data into a query. The best approach for this is to use a SELECT statement for each row and combine the results using UNION ALL. The revised version of the above sample script would be:

INSERT INTO Salespeople
    (Name, Area, SalesTarget)
SELECT
    'Bob', 'East', 100000
UNION ALL SELECT
    'Jim', 'West', 120000
UNION ALL SELECT
    'Mel', 'North', 110000
UNION ALL SELECT
    'Sue', 'Central', 120000

NB: You can also use the UNION command without the ALL clause to combine rows. This will cause any duplicated rows to be inserted only once. Using UNION ALL preserves the duplicates.

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Log Out / Değiştir )

Connecting to %s