Insert Into syntax - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Insert Into syntax

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-12-12 : 11:48:46

I have a very simple table with the following creation statement.

/****** Object: Table [dbo].[home] Script Date: 12/12/2008 10:47:16 AM ******/
CREATE TABLE [dbo].[home] (
[home_id] int IDENTITY(1, 1) NOT NULL,

[home] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK__ability__78B3EFCA]
PRIMARY KEY NONCLUSTERED ([home_id] ASC)
WITH FILLFACTOR = 100
 ON [PRIMARY]
)
ON [PRIMARY];
GO

I simply want to insert multiple records at one time.

INSERT INTO Testing.dbo.home(home)
VALUES ('NewYorkCity'), ('Los Angeles'), ('Rome'), ('Dallas')

And I get the following error:

SQL Server Database Error: Line 2: Incorrect syntax near ','.

I know this is stupid, and I've googled it without success. What the heck am I doing wrong?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 12:01:02

you cant insert in that way

try this

INSERT INTO dbo.home(home)
SELECT 'NewYorkCity' UNION ALL
SELECT 'Los Angeles' UNION ALL
SELECT 'Rome' UNION ALL
SELECT 'Dallas'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 12:47:15

quote:
Originally posted by DavidChel

I have a very simple table with the following creation statement.

/****** Object: Table [dbo].[home] Script Date: 12/12/2008 10:47:16 AM ******/
CREATE TABLE [dbo].[home] (
[home_id] int IDENTITY(1, 1) NOT NULL,
[home] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK__ability__78B3EFCA]
PRIMARY KEY NONCLUSTERED ([home_id] ASC)
WITH FILLFACTOR = 100
ON [PRIMARY]
)
ON [PRIMARY];
GO

I simply want to insert multiple records at one time.

INSERT INTO Testing.dbo.home(home)VALUES ('NewYorkCity')
INSERT INTO Testing.dbo.home(home)VALUES ('Los Angeles')
INSERT INTO Testing.dbo.home(home)VALUES ('Rome')
INSERT INTO Testing.dbo.home(home)VALUES('Dallas')

And I get the following error:

SQL Server Database Error: Line 2: Incorrect syntax near ','.

I know this is stupid, and I've googled it without success. What the heck am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:22:59

quote:
Originally posted by DavidChel

I have a very simple table with the following creation statement.

/****** Object: Table [dbo].[home] Script Date: 12/12/2008 10:47:16 AM ******/
CREATE TABLE [dbo].[home] (
[home_id] int IDENTITY(1, 1) NOT NULL,
[home] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK__ability__78B3EFCA]
PRIMARY KEY NONCLUSTERED ([home_id] ASC)
WITH FILLFACTOR = 100
ON [PRIMARY]
)
ON [PRIMARY];
GO

I simply want to insert multiple records at one time.

INSERT INTO Testing.dbo.home(home)
VALUES ('NewYorkCity'), ('Los Angeles'), ('Rome'), ('Dallas')

And I get the following error:

SQL Server Database Error: Line 2: Incorrect syntax near ','.

I know this is stupid, and I've googled it without success. What the heck am I doing wrong?

the above will work in sql 2008 though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources