INSERT INTO Statement Error

Question

Post reply

INSERT INTO Statement Error

gcruise

SSC Rookie

Points: 36
More actions
February 28, 2008 at 2:33 pm

#378966

Below is my SQL statement and the error i receive.
insert into intrf_ext_mapped_value( ext_system_id, provider_id, location_id, type_id, mapped_value, delete_ind, note, created_by, modified_by )
select (ext_system_id, provider_id, location_id, type_id, mapped_value, delete_ind, note, created_by, modified_by ) from intrf_ext_mapped_mpg
Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Thanks in advance...

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

When using a select to do an insert you do not use the parentheses in your select. Your code should look like:

INSERT INTO intrf_ext_mapped_value

(

ext_system_id,

provider_id,

location_id,

type_id,

mapped_value,

delete_ind,

note,

created_by,

modified_by

)

SELECT

ext_system_id,

provider_id,

location_id,

type_id,

mapped_value,

delete_ind,

note,

created_by,

modified_by

FROM

intrf_ext_mapped_mpg

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gcruise SSC Rookie Points: 36 More actions · Answer 2

gcruise

SSC Rookie

Points: 36

February 28, 2008 at 3:35 pm

#783490

Thanks Jack!!

😀

hanelson1510 SSC Enthusiast Points: 143 More actions · Answer 3

I got the same message on a more simple statement:

USE work_test1

INSERT INTO Student_Main

(Last_Name);

VALUES ("Jones")

I had many more fields, but even one field throws the same error reported above.

Help please.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Remove the semi-colon... change the double quotes to single quotes around 'James'.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hanelson1510 SSC Enthusiast Points: 143 More actions · Answer 5

Thank you so much. Your correction worked perfectly.

I have another question:

I have an ID unique identifier field which is the first field in the table.

ID(PK,uniqueidentifier, not null)

I am not listing this field in the insert because I expect SQL Server to create it.

Is this correct?

Also, by what method (SQL Server Mgmt Studio) do I verify that the insert worked?

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Ok, no... you have to use NEWID() to populate the column. It is NOT a self populating column. Then, you would check for an error... if no error, then the insert happened.

Lookup @@ERROR and RAISERROR in Books Online for a lot more detail on error handling.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shwetha-663739 Valued Member Points: 61 More actions · Answer 7

Hi,

If your ID column is uniqueidentifier with primary key, then you will be having two options,

1) if identity property of the column is false, then u will have to provide the value to that column.

2) if identity property of the column is true, u can set the identity seed to 1 and identity increment to 1.

In second case the ID column will increment whenever u insert without providing the value.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (3/4/2008)
Ok, no... you have to use NEWID() to populate the column. It is NOT a self populating column. Then, you would check for an error... if no error, then the insert happened.
Lookup @@ERROR and RAISERROR in Books Online for a lot more detail on error handling.

Well - it is "self-populating" if you MAKE it.... (like - putting in NEWID() as the default value).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

hanelson1510 SSC Enthusiast Points: 143 More actions · Answer 9

So this should work?

USE work_test1

INSERT INTO Student_Main

(ID,First_Name, Last_Name, Current_Grade, Current_School)

VALUES (NEWID(),'Herbert','Jones',10,'Penn Hills')

Also, aside from error checking, I was asking a more basic question. Should I now be able to query the db to see that the record has been added? Like this?

Select ID, Last_Name, First_Name, Current_Grade, Current_School)

from work_test1

Where last name = 'jones'

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

Looks like it should work.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TraderSam SSCrazy Points: 2931 More actions · Answer 11

Yes, your select should return the row you entered (if it is there).

Check the 'from'...you have the database name there...this should be the table name, 'student_main'.

If it was easy, everybody would be doing it!;)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

One more little syntax problem - you have a rogue ) in your SELECT statement. get rid of that.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Matt Miller (3/5/2008)
Jeff Moden (3/4/2008)
Ok, no... you have to use NEWID() to populate the column. It is NOT a self populating column. Then, you would check for an error... if no error, then the insert happened.
Lookup @@ERROR and RAISERROR in Books Online for a lot more detail on error handling.
Well - it is "self-populating" if you MAKE it.... (like - putting in NEWID() as the default value).

True enough... but for a bit of GUI code, you'd never know what the value of the NEWID was for the row you just inserted. Pretty sure that SCOPE_IDENTITY isn't going to help you there 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Jeff - true enough. It would make that tough in the absence of surrogate identifiers. Of course - I then plan "other ways" to get those (like a batchID, etc...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?