|
Home > Archive > MS SQL Server MSEQ > September 2005 > Insert Multiple Rows into a Table Without Using Select
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Insert Multiple Rows into a Table Without Using Select
|
|
|
| Hi.
I want to insert 4 rows into a SQL table.
Can I do this without using 4 Insert statements?
This is my SQL statement:
Insert Into MyTable Values (1,2)
(3,4)
(5,6)
(7,8)
but I'm getting a compile error.
I tried using a comma after each row (except the last) but that didn't work.
TIA,
Rita
| |
| Hugo Kornelis 2005-09-28, 8:24 pm |
| On Wed, 28 Sep 2005 10:59:03 -0700, RitaG wrote:
>Hi.
>
>I want to insert 4 rows into a SQL table.
>Can I do this without using 4 Insert statements?
>
>This is my SQL statement:
> Insert Into MyTable Values (1,2)
> (3,4)
> (5,6)
> (7,8)
>but I'm getting a compile error.
>I tried using a comma after each row (except the last) but that didn't work.
Hi Rita,
The subject suggests that you don't want to use SELECT (why??); if
that's the case, you'll have to use 4 INSERT .. VALUES statements.
Here's how to do it in one INSERT .. SELECT, using UNION:
INSERT INTO MyTable (Column1, Column2)
SELECT 1, 2
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 5, 6
UNION ALL
SELECT 7, 8
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
| |
|
| Thanks Hugo for your response.
I have 4 lines that I want to insert into a table in 30 databases.
The column values are always the same for the 30 databases and I don't need
a "Select" statement.
"Hugo Kornelis" wrote:
> On Wed, 28 Sep 2005 10:59:03 -0700, RitaG wrote:
>
>
> Hi Rita,
>
> The subject suggests that you don't want to use SELECT (why??); if
> that's the case, you'll have to use 4 INSERT .. VALUES statements.
>
> Here's how to do it in one INSERT .. SELECT, using UNION:
>
> INSERT INTO MyTable (Column1, Column2)
> SELECT 1, 2
> UNION ALL
> SELECT 3, 4
> UNION ALL
> SELECT 5, 6
> UNION ALL
> SELECT 7, 8
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
| |
| Hugo Kornelis 2005-09-29, 8:24 pm |
| On Wed, 28 Sep 2005 13:22:02 -0700, RitaG wrote:
>Thanks Hugo for your response.
>I have 4 lines that I want to insert into a table in 30 databases.
>The column values are always the same for the 30 databases and I don't need
>a "Select" statement.
Hi Rita,
Well, you can either have 4 INSERT statements with a VALUES clause, or
one INSERT statement with a UNION'ed SELECT clause, as I wrote in my
previous reply. There are no other alternatives.
But I guess the real question is: why do you store the same data in 30
identical tables in 30 databases? Maybe it's time to get rid of some of
the redundancy?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
| |
|
| :-) Sorry, I misinformed you!
I'm inserting the 4 rows 30 times into the same database but with a
different key.
We do processing of "files in and files out" for different entities. The
files in and files out for each entity can be different and so we need an
entry for each entity. We just received 30 different entities that need
processing. They all have 2 files in and 2 files out - hence the 4 rows per
entity.
"Hugo Kornelis" wrote:
> On Wed, 28 Sep 2005 13:22:02 -0700, RitaG wrote:
>
>
> Hi Rita,
>
> Well, you can either have 4 INSERT statements with a VALUES clause, or
> one INSERT statement with a UNION'ed SELECT clause, as I wrote in my
> previous reply. There are no other alternatives.
>
> But I guess the real question is: why do you store the same data in 30
> identical tables in 30 databases? Maybe it's time to get rid of some of
> the redundancy?
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|
|
|
|
|