Multirow insert statement,... how?

In MySQL I was able to insert multiple rows into the db like this:

REPLACE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

In transact-Sql (Sql Server 2000), this "REPLACE" keyword means something different. What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row.

How do I do this in SQL Server 2000?

Also, I can do this in MySQL:

INSERT INGORE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

This meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will ignore that new row's insert and keep the values of the old row.

How do I do that also in SQL Server 2000?

ThanksYou can't easily. MySQL is CISC to MS SQLs RISC.
There is no equiv' REPLACE and no IGNORE. You have do everything the hard way.

You can use insert with a not exists (yuk) or populate a temp table var with the rows you want and load the same keyed values from the database. That way you can see the gaps (inserts) and the ones with data (updates). Still not gr8 but what you gonna do!|||Darn,... I have experience developing aps with both MySQL and SQL server and I think this is the first time ever that it's harder to do something with SQL SERVER. Usually, it's MySql that makes things harder.

No wonder why I couldn't find any documentation for the life of me.
Oh well!|||You could use, and I'm trying not to be sick when saying it, a datagram. I think that's got some of the features you want <shudder>. Might be worth a look though.|||How do I use a datagram? Can you give me some examples?|||try
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_8704.asp|||You can insert multiple rows into a table by doing the following (works for SQL 7 & 2000, not sure about MySQL):

insert into yourtable(field1, field2, field3)
select
customerid,
firstname,
lastname
from
customers
where
customerid between 1 and 10

This would insert several rows from the customers table into yourtable.|||youre kiddin me right tingent? That is absolutely off the subject of what we are talking about|||"What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row."

Surely, though, by definition you will only ever have ONE row that matches the primary key so isn't this in effect an UPDATE statement? Or does the replace statement insert a new row if a match isn't found (you didn't mention it did!) ?|||Ouch...ok, I admit I didn't completly read the post so I didn't know what REPLACE was doing in MySQL. My bad...|||But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement.|||>> But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement

True but you do need to read the question. I think you caused a tiny little bit of offense 'cause judging by the question I think they know how do a standard insert. Don't worry though, no ones perfect - and don't let some of the posters tell you otherwise ;)|||I apologize, I didn't mean it at all the way it sounded. Sometimes things don't come off the right way when you type them over the internet. If we were in person and I would've said that to you, you would've took it the right way because I would've said it in a joking way.

Sorry|||Tingent, again, I apologize for the way that sounded.

The Mult-Row insert statement that you have extracts rows from another area and inserts them into the table. I need to insert brand new rows.|||hat's off to you javan15, what I jolly good egg you are.