Inserting multiple rows into a table

Question

Inserting multiple rows into a table

rshafer

SSChasing Mays

Points: 655
More actions
December 9, 2008 at 12:27 pm

#129209

I'm trying to insert multiple rows using the T-SQL below. Is there a more efficient way to do this? I've read a couple posts on here about using the UNION ALL statement.
USE Test
GO
INSERT INTO Practice
VALUES ('John', 'Jones', '99980', '45', 'Payson', 'Arizona')
INSERT INTO Practice
VALUES ('Mary','Jones','99982','25','Payson','Arizona')
INSERT INTO Practice
VALUES ('Eric','Edwards','88232','32','San Diego','California')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

rshafer (12/9/2008)
I'm trying to insert multiple rows using the T-SQL below. Is there a more efficient way to do this? I've read a couple posts on here about using the UNION ALL statement.
USE Test
GO
INSERT INTO Practice
VALUES ('John', 'Jones', '99980', '45', 'Payson', 'Arizona')
INSERT INTO Practice
VALUES ('Mary','Jones','99982','25','Payson','Arizona')
INSERT INTO Practice
VALUES ('Eric','Edwards','88232','32','San Diego','California')

Yes UNION ALL is the way about it. If you can use SQL2008 then row constructors with VALUES use a more compact syntax

* Noel

rshafer SSChasing Mays Points: 655 More actions · Answer 2

Unfortunately I am stuck with 2005 for now. Can you please provide an example using the UNION ALL statement?

k man SSC-Addicted Points: 432 More actions · Answer 3

USE Test

GO

INSERT INTO Practice

select 'John', 'Jones', '99980', '45', 'Payson', 'Arizona'

union all

select 'Mary','Jones','99982','25','Payson','Arizona'

union all

select 'Eric','Edwards','88232','32','San Diego','California'

Cheers,

K

rshafer SSChasing Mays Points: 655 More actions · Answer 4

k man (12/9/2008)
USE Test
GO
INSERT INTO Practice
select 'John', 'Jones', '99980', '45', 'Payson', 'Arizona'
union all
select 'Mary','Jones','99982','25','Payson','Arizona'
union all
select 'Eric','Edwards','88232','32','San Diego','California'
Cheers,
K

Thank you! That worked great.