Odd problem with INSERT and commas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Odd problem with INSERT and commas

Author

Topic

DallasX
Starting Member

3 Posts

Posted - 2006-01-09 : 13:28:30

Hey everyone, I am new to the forum, woohoo! :)

I am using SQL Server 2000.

When I have this statement:


INSERT INTO states (sid, name, abbr)
VALUES
(1,'Alabama','AL'),(2,'Arkansas','AR')

There is an error that tells me there is something wrong near the , (comma) when I use two values. When I use one value, it works. What the heck?

Of course it does not help that I did most of my db work in Oracle when I was in college. Maybe I am overlooking something?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-09 : 13:37:37

invalid syntax
INSERT INTO states (sid, name, abbr)
VALUES (1,'Alabama','AL')
INSERT INTO states (sid, name, abbr)
(2,'Arkansas','AR')

or you could
INSERT INTO states (sid, name, abbr)
select (1,'Alabama','AL'
union all
select 2,'Arkansas','AR'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-09 : 13:38:44

u have to have individual statements:
INSERT INTO states (sid, name, abbr) VALUES (1,'Alabama','AL')
INSERT INTO states (sid, name, abbr) VALUES (2,'Arkansas','AR')

DallasX
Starting Member

3 Posts

Posted - 2006-01-09 : 13:47:37

quote:
Originally posted by nr

invalid syntax
INSERT INTO states (sid, name, abbr)
VALUES (1,'Alabama','AL')
INSERT INTO states (sid, name, abbr)
(2,'Arkansas','AR')

or you could
INSERT INTO states (sid, name, abbr)
select (1,'Alabama','AL'
union all
select 2,'Arkansas','AR'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Why is that invalid? I thought SQL statements were standard.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-09 : 13:53:24

For any language there is a syntax and SQL being Structured Query "Language", it has the same. To get it done by SQL u should use proper syntax
Eg. if u ask somebody in English the following what would be the reaction
Give me Soap Water

DallasX
Starting Member

3 Posts

Posted - 2006-01-09 : 13:59:38

quote:
Originally posted by Srinika

For any language there is a syntax and SQL being Structured Query "Language", it has the same. To get it done by SQL u should use proper syntax
Eg. if u ask somebody in English the following what would be the reaction
Give me Soap Water

Heheheh

I would probably say, "You mean give me some soapy water."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-09 : 14:13:07

But I want Soap, Water (means each stuff seperately)
Since u have similar syntax but u also mis-interpreted mine, so how does innocent SQL know what is in ur mind by saying it in a way not confirming to its syntax

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-10 : 00:53:30

Learn SQL
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources