SQL Server query error

Question

According to Wikipedia this syntax looks correct...

INSERT INTO dbo.metadata_type ("name", "publishable") 
VALUES
("Content Owner", 0),
("Content Coordinator", 0),
("Writer", 0),
("Content Type", 0),
("State", 1),
("Business Segment", 0),
("Audience", 0),
("Product Life Cycle Stage", 0),
("Category", 0),
("Template", 0)

I'm getting errors. I've tried wrapping the column names in ` but that didn't work either...

Error code 207, SQL state 42S22: Invalid column name 'Content Owner'.
Error code 207, SQL state 42S22: Invalid column name 'Content Coordinator'.
Error code 207, SQL state 42S22: Invalid column name 'Writer'.
Error code 207, SQL state 42S22: Invalid column name 'Content Type'.
Error code 207, SQL state 42S22: Invalid column name 'State'.

Besides using single quotes - this syntax of specifying multiple tuples in a single INSERT statement is valid in SQL Server 2008 and newer only - it doesn't work in earlier versions. — marc_s
– marc_s, Commented Mar 27, 2012 at 17:22

Cristian Lupascu · Accepted Answer · 2012-03-27 17:05:21Z

13

In SQL Server, string values are delimited by ', not".

Also, column names should either be enclosed in square brackets, or left as they are (if they don't contain spaces).

Your query should, therefore, look like this:

INSERT INTO dbo.metadata_type (name, publishable) VALUES
    ('Content Owner', 0),
    ('Content Coordinator', 0),
    ('Writer', 0),
    ('Content Type', 0),
    ('State', 1),
    ('Business Segment', 0),
    ('Audience', 0),
    ('Product Life Cycle Stage', 0),
    ('Category', 0),
    ('Template', 0)

answered Mar 27, 2012 at 17:05

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Francis P · Accepted Answer · 2012-03-27 17:08:47Z

3

You must use Single quotes instead of double quotes for your values and no quotes at all to specify which column to insert:

INSERT INTO dbo.metadata_type (name, publishable) VALUES
('Content Owner', 0),
('Content Coordinator', 0),
('Writer', 0),
('Content Type', 0),
('State', 1),
('Business Segment', 0),
('Audience', 0),
('Product Life Cycle Stage', 0),
('Category', 0),
('Template', 0)

answered Mar 27, 2012 at 17:08

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Comments

Gabriel Scavassa · Accepted Answer · 2012-03-27 17:29:29Z

3

The 'Error 207' of sql is related to the incorrect column name of the table. I seems that you are trying to retrieve the data about the column name which doesn't exist in the specified table. I suggest you to make sure that your are using correct column name in you query. Also check the table name is correct mentioned in query or not. Try this and let me know if you are still getting same error or not.

if you are trying to insert values to a Varchar using "" try to use simple ''

like:

INSERT INTO dbo.metadata_type (name, publishable) VALUES
('Content Owner', 0),
('Content Coordinator', 0),
('Writer', 0),
('Content Type', 0),
('State', 1),
('Business Segment', 0),
('Audience', 0),
('Product Life Cycle Stage', 0),
('Category', 0),
('Template', 0)

edited Mar 27, 2012 at 17:29

answered Mar 27, 2012 at 17:09

Gabriel Scavassa

6041 gold badge5 silver badges22 bronze badges

2 Comments

Francis P Over a year ago

your query contains errors: column names must not be surrounded by quotes.

Gabriel Scavassa Over a year ago

@FrancisP i'm using Microsoft SQL and it works fine like: INSERT INTO dbo.metadata_type ("name", "publishable"); But i take it out... if u think it's better...

Chetter Hummin · Accepted Answer · 2012-03-27 17:10:07Z

0

If you're looking to insert multiple rows with one insert statement, here's another way to do it

INSERT INTO dbo.metadata_type (name, publishable) 
SELECT 'Content Owner', 0
UNION ALL
SELECT 'Content Coordinator', 0
UNION ALL

and so on

answered Mar 27, 2012 at 17:10

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL Server query error

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related