Insert more then one records at a time.  
Author Message
tony_pez





PostPosted: Fri Mar 10 09:08:09 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time.

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

SQL Server51  
 
 
Green





PostPosted: Fri Mar 10 09:08:09 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time. In SQL Server, it will be:

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

 
 
Grant





PostPosted: Fri Mar 10 09:10:36 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time. sweet man... thanks man...






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


 
 
Bob





PostPosted: Fri Mar 10 09:16:24 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time.
> 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.


 
 
Grant





PostPosted: Fri Mar 10 09:23:45 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time. 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.






> sweet man... thanks man...
>
>
>
>


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


 
 
Conor





PostPosted: Mon Mar 13 23:56:37 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time. (union removes duplicates. If you don't need this guarantee, union all is
what you need and is a bit faster).

Thanks,

Conor




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


 
 
Bob





PostPosted: Mon Mar 20 08:46:08 CST 2006 Top

SQL Server Developer >> Insert more then one records at a time. Correct. I should not have assumed the OP knew this.


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



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