CODEWITHSUNDEEP
sqlsql-server-2005
Nick Rolando
Nick Rolando

Reputation: 26157

INSERT statement with existing records

I was wondering if it is possible to do an insert statement with records that already exist. For example:

insert into tbl(item_name, item_price) values(select item_name, item_price from tbl where id = 5)

Say id is an auto incrementing pk.

When I try something similar to this, I'm getting errors:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

Am I missing something, or is this just not possible?

Upvotes: 3

Views: 7366

Answers (3)

Marek Kwiendacz
Marek Kwiendacz

Reputation: 9814

Try this: 

insert into tbl(item_name, item_price) select item_name, item_price from tbl where id = 5

Upvotes: 3

FerranB
FerranB

Reputation: 36767

Checkout this article: Adding Rows by Using INSERT and SELECT

Anyway the right way is the following:

insert into tbl(item_name, item_price) 
select item_name, item_price 
  from tbl 
 where id = 5

Upvotes: 7

Daisetsu
Daisetsu

Reputation: 4976

A subquery is possible in MySQL, but if you're doing it like this then your database is probably not normalized properly, and a design change would be your best move.

Here's the syntax for a subquery: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Upvotes: 0

Related Questions