Multiple row insert into a table (stupid, yes)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple row insert into a table (stupid, yes)

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2008-03-31 : 10:45:33

Hi guys,

Okay, I really should know the answer to this by now, but the answer escapes me.

--Let's say I have a simple table:

create table #mytable
(
id varchar(3)
)
go

--Instead of doing this:
insert into #mytable
values
('ABC')
go
insert into #mytable
values
('DEF')
go

select * from #mytable
go

drop table #mytable
go

/*
I'd like to do this (since I might have anywhere from 20 to 50 of these, etc.).

Like:

insert into #mytable
values
('ABC','DEF','GHI','JKL')
go

(Obviously this doesn't work since I need to repeat the column name however many times) - but isn't there a simpler way to insert multiple rows into an unpopulated table?

Unfortunately, I don't have the data in another table somewhere already, so I need to manually populate, but was wondering if there was a convenient way of doing this.

Any help much appreciated!

*/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 10:47:14

[code]insert into #mytable
select 'DEF' union all
select 'ABC' union all
select ...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-03-31 : 10:47:40

insert into #mytable
select 'ABC'
UNION ALL
select 'DEF'

etc ad nauseam

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-03-31 : 10:47:59

dang you harsh!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2008-03-31 : 12:15:28

Okay, thanks. Better than nothing.

-KS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources