Insert more then one records at a time.

10 Mar 2006 3:00 PM

Grant

In MySQL (sorry for saying the bad word here) I could do this.

insert into tbl (field1, field2) values ('apple', 'yuck'), ('orange',
'yum');

The syntax above allows me to insert multiple records using one insert
statement. It does not work in SQL Server 2000 (MSDE 2000).

10 Mar 2006 3:08 PM

Green

In SQL Server, it will be:

insert into tbl (field1, field2) values
('apple', 'yuck') union
('orange', 'yum');

10 Mar 2006 3:10 PM

Grant

sweet man... thanks man...

Show quote

"Green" <subhash.d***@gmail.com> wrote in message
news:1142003289.075181.79690@z34g2000cwc.googlegroups.com...
> In SQL Server, it will be:
>
> insert into tbl (field1, field2) values
> ('apple', 'yuck') union
> ('orange', 'yum');
>

10 Mar 2006 3:23 PM

Grant

umm... did not work on mine but I able to search the web based on what you
told me.

this works on mine.
    insert into tbl (field1, field2)
    select 'apple', 'yuck'
    union all
    select 'orange', 'yum'

link.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=897&lngWId=5

Thanks.

Show quote

"Grant" <em***@nowhere.com> wrote in message
news:uCEFYUFRGHA.1728@TK2MSFTNGP11.phx.gbl...
> sweet man... thanks man...
>
>
>
>
> "Green" <subhash.d***@gmail.com> wrote in message
> news:1142003289.075181.79690@z34g2000cwc.googlegroups.com...
>> In SQL Server, it will be:
>>
>> insert into tbl (field1, field2) values
>> ('apple', 'yuck') union
>> ('orange', 'yum');
>>
>
>

10 Mar 2006 3:16 PM

Bob Barrows [MVP]

Green wrote:

> In SQL Server, it will be:
>
> insert into tbl (field1, field2) values
> ('apple', 'yuck') union
> ('orange', 'yum');

Almost. union can only be used with select statements. It should be:

insert into tbl (field1, field2)
select 'apple', 'yuck'
union
select 'orange', 'yum'

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

14 Mar 2006 5:56 AM

Conor Cunningham [MS]

(union removes duplicates. If you don't need this guarantee, union all is
what you need and is a bit faster).

Thanks,

Conor

Show quote

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eCghbWFRGHA.5552@TK2MSFTNGP14.phx.gbl...
> Green wrote:
>> In SQL Server, it will be:
>>
>> insert into tbl (field1, field2) values
>> ('apple', 'yuck') union
>> ('orange', 'yum');
>
> Almost. union can only be used with select statements. It should be:
>
> insert into tbl (field1, field2)
> select 'apple', 'yuck'
> union
> select 'orange', 'yum'
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

20 Mar 2006 2:46 PM

Bob Barrows [MVP]

Correct. I should not have assumed the OP knew this.

Conor Cunningham [MS] wrote:
Show quote

> (union removes duplicates. If you don't need this guarantee, union
> all is what you need and is a bit faster).
>
> Thanks,
>
> Conor
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eCghbWFRGHA.5552@TK2MSFTNGP14.phx.gbl...
>> Green wrote:
>>> In SQL Server, it will be:
>>>
>>> insert into tbl (field1, field2) values
>>> ('apple', 'yuck') union
>>> ('orange', 'yum');
>>
>> Almost. union can only be used with select statements. It should be:
>>
>> insert into tbl (field1, field2)
>> select 'apple', 'yuck'
>> union
>> select 'orange', 'yum'
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.