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, 2009 at 02:54 PM in Default

avatar image

user-753
17 1 1 3

If you provide your table structure and statements a better answer can be provided.

Dec 14, 2009 at 03:33 PM Jonathan Kehayias
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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, 2009 at 04:57 PM

avatar image

Raj More
1.8k 82 87 90

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

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, 2009 at 06:25 PM

avatar image

Scott P
36 2 1

(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, 2009 at 03:33 PM

avatar image

Jonathan Kehayias
293 1 3 6

(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, 2009 at 04:22 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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.

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:

x137

asked: Dec 14, 2009 at 02:54 PM

Seen: 1616 times

Last Updated: Dec 14, 2009 at 02:54 PM

Copyright 2016 Redgate Software. Privacy Policy