Hi,


i am trying to insert data two table with single query with
foreign key reference but its not working
my query is


BEGIN TRANSACTION
DECLARE @salid int
Insert into sal_products (upc,shelf_number,aisle_no) values
('123asd','N/A','N/A','N/A','N/A',100)
select @salid = scope_identity()
insert into sal_product_locations (sal_product_id,side,position)
values (@salid,'N/A','N/A')
commit


could any one provide me solution for this

thanks,
-pab

On Wed, 23 Nov 2011 21:20:11 -0800 (PST), Pab
<prabu.net88 (AT) gmail (DOT) com> wrote:

"Is not working" does not tell us much. Please be more
specific in your problem description.


This code has several errors. I cannot imagine this is
the actual code you used. The error messages for these
errors are pretty clear.


We might once you tell us what the problem is.
Best regards,
--
( Kees Nuyt
)
c[_]

El 24/11/2011 6:20, Pab escribió/wrote:
As a general rule, when your code is not working the solution is to fix
it so it works

Where's the single query here? I can see three queries. You can't just
omit the query separator (";" by default) and expect that MySQL will
magically compose a single query.

The answer to your question (insert data in two tables with single
query) can be found in the FAQ section of the manual:

http://dev.mysql.com/doc/refman/5.5/...daitem-B-1-1-4

However, I can't really see how your code attempt matches the question :-?



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

In article <f1305dde-265c-4979-b184-6c9e9f7975a2 (AT) z22g2000prd (DOT) googlegroups.com>,
Pab <prabu.net88 (AT) gmail (DOT) com> wrote:
This is a MySQL newsgroup. Your code above looks like it is for MS
SQL Server. If it is, then you should try comp.databases.ms-sqlserver
instead.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

in the above query i am trying to insert values to sal_products table
and then i am taking that id using scope_identity and then i am trying
to insert that id with some values in sal_product_locations table but
is show some error near aisle_' which is error after that under score
symbol


thanks,
-pab

On 11/24/2011 8:24 AM, Pab wrote:
As Tony said - this looks like Microsoft SQL Server. This newsgroup is
for MySQL, an entirely different RDBMS.

Please ask your question in comp.databases.ms-sqlserver.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 24-11-2011 06:20, Pab wrote:
After the tablename (sal_products) you specify 3 field names
(upc,shelf_number,ais_no), but after values you have 5 values
('123asd','N/A','N/A','N/A','N/A',100)

This cannot be correct.


As others said, please provide the exact error message!

--
Luuk

actually i need mysql query only
how can i perform insert data in two table with single query and i
also need id from first table to insert that id to second table column
sal_product_id

Insert into sal_products
(upc,store_area,section_aisle,shelf_number,aisle_n o,location) values
('123asd','N/A','N/A','N/A','N/A',100)
insert into sal_product_locations (sal_product_id,side,position)

pls provide me a solution


thanks,
-pab

Pab <prabu.net88 (AT) gmail (DOT) com> wrote:

You cannot.
But you can use two queries as in the example you have shown.

While I have not seen you using an auto-generated id in your
example; MySQL does have AUTO_INCREMENT columns and you can
refer to the last generated values with the LAST_INSERT_ID()
SQL function. Like so:

BEGIN;
INSERT INTO sal_products (...) VALUES (...);
INSERT INTO sal_product_locations (...) VALUES (LAST_INSERT_ID(), ...);
COMMIT;

This is one transaction with 2 queries.

You want to read the fine manual:

http://dev.mysql.com/doc/refman/5.1/en/insert.html
http://dev.mysql.com/doc/refman/5.1/...increment.html
http://dev.mysql.com/doc/refman/5.1/en/commit.html


XL