x

Do Have To Run Multiple Inserts?

Currently when I have to insert multiple rows into a table the only way I know how to do it is to just brute force it. For example if I need to insert 6 product IDs for a person I code inserts which seems inelegant; is there any other way?

more ▼

asked Dec 14 '09 at 02:54 PM in Default

user-753 gravatar image

user-753
17 1 1 1

If you provide your table structure and statements a better answer can be provided.
Dec 14 '09 at 03:33 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

Another common method I've seen is like this:

Declare @People Table( EmployeeId Int Identity (1, 1), FirstName VarChar (100), LastName VarChar (100))

INSERT @People (FirstName, LastName) SELECT 'John', 'Doe' UNION ALL SELECT 'James', 'Doe' UNION ALL SELECT 'Jill', 'Doe' UNION ALL SELECT 'Bo', 'Bo'

SELECT * FROM @People
more ▼

answered Dec 14 '09 at 06:25 PM

Scott P gravatar image

Scott P
36

Wow! Even better for those older SQL versions.
Dec 14 '09 at 06:58 PM user-753
Keep in mind that this can't be done easily using parameterization so you either have a SQL Injection risk or complex code to handle adding a parameter for each column, for each row dynamically.
Dec 14 '09 at 09:45 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

INSERT - VALUES

Depending on your version of SQL Server, you may not have to use multiple SQL Statements. In SQL Server 2008, you can use multiple comma-separated values for INSERT statements.

Here is a working example

Declare @People Table
(
    EmployeeId	Int Identity (1, 1),
    FirstName	VarChar (100),
    LastName	VarChar (100)
)

INSERT @People 
Values 
('John', 'Doe'),
('James', 'Doe'),
('Jill', 'Doe'),
('Bo', 'Bo')

SELECT * FROM @People

Importing through Bulk Import

You can use text/CSV/Native format files and Bulk Import to fill data into your tables.

more ▼

answered Dec 14 '09 at 04:57 PM

Raj More gravatar image

Raj More
1.7k 77 82 84

I like the multiple VALUE clause approach. Nice and clean.
Dec 14 '09 at 05:13 PM David Wimbush
This is exactly what I was looking for! Thank you!
Dec 14 '09 at 05:47 PM user-753
Same comment as on the other method like this. This has the exact same problem. However, if you use SQL 2008, a Table Valued Parameter solves that problem. http://msdn.microsoft.com/en-us/library/bb675163.aspx
Dec 14 '09 at 09:33 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

Another mechanism is to pass a comma-delimited list and pivot it into a table, preferably through the use of queries, but possibly through user defined functions.

more ▼

answered Dec 14 '09 at 04:22 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

A common method of doing this is to pass an XML document to a stored procedure and use OPENXML or XQUERY to shred the XML document and perform the insert in a single statement.

more ▼

answered Dec 14 '09 at 03:33 PM

Jonathan Kehayias gravatar image

Jonathan Kehayias
283 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103

asked: Dec 14 '09 at 02:54 PM

Seen: 1266 times

Last Updated: Dec 14 '09 at 02:54 PM