Multiple Inserts with MS SQL Server

[esdee]

in MySQL there is this syntax for using a single insert for multiple records:

INSERT INTO x (a,b)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

is there similar syntax in MS SQL Server 2000 ?
i came up with

insert  (a, b)
select '1', 'one' 
union 
select '2', 'two'
union 
select '3', 'three'

but im not sure about its performance.

another approach i discovered is in

http://www.windowsitpro.com/SQLServer/Article/ArticleID/ 21071/ 21071.html

: passing a string with delimited values, parsing and inserting, but wanted to see other ways.

my case is an Application in c#, passing the values in an xml to the DB, but with large xmls it ... chokes...

------------------------

http://esdeeblog.blogspot.com/

 

[hmckillop]

You can pass XML and then do a straight insert from it.

e.g.

declare @pi_XMLUpdateString varchar(1000)
SET @pi_XMLUpdateString = '<root>
		<node1>
			<subnode a="1" b="one" />
			<subnode a="2" b="two" />
			<subnode a="3" b="three" />
		</node1>
	</root>'
--Execute the inbuilt XML stored proc handler
EXEC dbo.sp_xml_preparedocument @FileHandle output, @pi_XMLUpdateString 
INSERT INTO tableX (a, b)
SELECT 
	*
FROM
	OPENXML(@FileHandle, 'root/node1/subnode', 1 ) 
	WITH (	accountOID       Int         	'@a',
		status 		Int 		'@b')

EXEC dbo.sp_xml_removedocument @FileHandle

"I'm living so far beyond my income that we may almost be said to be living apart

 

[esdee]

my current implementation is exactly with xml and it chokes hard with large xmls

------------------------

http://esdeeblog.blogspot.com/