Multiple insert

Hi, I'm trying to insert multiple rows with the following statment and
am getting a syntax error on line 3. The datatypes are varchars except
for status which is numeric.

insert S (S#, SNAME, STATUS, CITY)
values
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');

What's wrong?
Thanks,
SashiYou have to use the INSERT... SELECT form to insert multiple rows.

INSERT INTO S (s#, sname, status, city)
SELECT 'S2', 'Jones', 10, 'Paris' UNION ALL
SELECT 'S3', 'Blake', 30, 'Paris' UNION ALL
SELECT 'S4', 'Clark', 20, 'London' UNION ALL
SELECT 'S5', 'Adams', 30, 'Athens' ;

--
David Portas
SQL Server MVP
--|||The real problem is that SQL Server does not yet support SQL-92 syntax,
in spite of having the power to do so. Your choices are:

1) use a series of INSERT INTO statements (notice that INSERT is a
proprietary shorthand, not Standard SQL).

2) use a proprietary SELECT ..UNION ALL chain to build a table the same
way that the VALUES table constructor would.

multiple field statment

I have a table that has 9 fields.
in some cases a record may almost be a duplicate of another record except
for one of the fields.
so I was thinking I need to write a statment to where I have to say what
each field equals, but I haven't had any luck
here is what I tried.
Select * from MyTable where field1 = "this" and field2 = "a" and field3 =
"test"
How do I write that statement correctly?
Thanks
Replace " with '. SQL uses a single quote to delimit literal strings rather
than a double quote. If that doesn't solve your problem then please tell us
exactly the wording of any error message you are getting.
David Portas
SQL Server MVP
sql