Inserting data into mutliple tables using SQL Insert Command 
Author Message
 Inserting data into mutliple tables using SQL Insert Command

Hello,
I am having trouble inserting data in two tables I have defined in ms
access.
The tables are as follows

Table Name:            Fields:
customer                  autonum, first,  last, middle

address                    addresspk, autonum, address, state, zipcode

Such that autonum is a primary key for the customer table and autonum for
address is a number.  The two tables are related by autonum (1 customer to
many addresses.  The relationship is setup between customer.autonum and
address.autonum)

How would I use the insert sql statement to insert data in both tables at
the same time?  Thank you,  Any input is appreciated.

Rich



Sun, 01 May 2005 22:44:24 GMT  
 Inserting data into mutliple tables using SQL Insert Command
Firstly it will need to be two SQL statements because the insert
statement only works on one table at a time. So you need to insert the
customer, find out the value of the new row's AutoNum column, and use
that value to put on the address record. How do you get the AutoNum
value? Well the traditional way is to add the record to an updateable
Recordset so you can see the value. But I read somewhere the other day

Server. I tried it and it works (VB6 SP5 connecting to Access 2000 via
ADO 2.7, Jet 4 OLEDB Provider) but I can't find any documentation of
it, so I don't know what you'd get if, for instance, another user did

worth considering.


Quote:
> Hello,
> I am having trouble inserting data in two tables I have defined in ms
> access.
> The tables are as follows

> Table Name:            Fields:
> customer                  autonum, first,  last, middle

> address                    addresspk, autonum, address, state, zipcode

> Such that autonum is a primary key for the customer table and autonum for
> address is a number.  The two tables are related by autonum (1 customer to
> many addresses.  The relationship is setup between customer.autonum and
> address.autonum)

> How would I use the insert sql statement to insert data in both tables at
> the same time?  Thank you,  Any input is appreciated.

> Rich



Mon, 02 May 2005 05:38:54 GMT  
 Inserting data into mutliple tables using SQL Insert Command


Quote:
> Firstly it will need to be two SQL statements because the insert
> statement only works on one table at a time. So you need to insert the
> customer, find out the value of the new row's AutoNum column, and use
> that value to put on the address record. How do you get the AutoNum
> value? Well the traditional way is to add the record to an updateable
> Recordset so you can see the value. But I read somewhere the other day

> Server. I tried it and it works (VB6 SP5 connecting to Access 2000 via
> ADO 2.7, Jet 4 OLEDB Provider) but I can't find any documentation of
> it, so I don't know what you'd get if, for instance, another user did

> worth considering.


> > Hello,
> > I am having trouble inserting data in two tables I have defined in ms
> > access.
> > The tables are as follows

> > Table Name:            Fields:
> > customer                  autonum, first,  last, middle

> > address                    addresspk, autonum, address, state, zipcode

> > Such that autonum is a primary key for the customer table and autonum for
> > address is a number.  The two tables are related by autonum (1 customer to
> > many addresses.  The relationship is setup between customer.autonum and
> > address.autonum)

> > How would I use the insert sql statement to insert data in both tables at
> > the same time?  Thank you,  Any input is appreciated.

> > Rich



that if you insert into a second table with autoincrement, the first
value will be lost, so you need to do this immediately after the insert
that you need the AutoIncrement value from. At least this is how it
works in SQL2000. Another user's insert would be under a different
connection, so would not affect your value.

Dan



Mon, 02 May 2005 08:15:50 GMT  
 Inserting data into mutliple tables using SQL Insert Command
Thanks guys.  I should be good to go now!  :)


Quote:


> > Firstly it will need to be two SQL statements because the insert
> > statement only works on one table at a time. So you need to insert the
> > customer, find out the value of the new row's AutoNum column, and use
> > that value to put on the address record. How do you get the AutoNum
> > value? Well the traditional way is to add the record to an updateable
> > Recordset so you can see the value. But I read somewhere the other day

> > Server. I tried it and it works (VB6 SP5 connecting to Access 2000 via
> > ADO 2.7, Jet 4 OLEDB Provider) but I can't find any documentation of
> > it, so I don't know what you'd get if, for instance, another user did

> > worth considering.




- Show quoted text -

Quote:
> > > Hello,
> > > I am having trouble inserting data in two tables I have defined in ms
> > > access.
> > > The tables are as follows

> > > Table Name:            Fields:
> > > customer                  autonum, first,  last, middle

> > > address                    addresspk, autonum, address, state, zipcode

> > > Such that autonum is a primary key for the customer table and autonum
for
> > > address is a number.  The two tables are related by autonum (1
customer to
> > > many addresses.  The relationship is setup between customer.autonum
and
> > > address.autonum)

> > > How would I use the insert sql statement to insert data in both tables
at
> > > the same time?  Thank you,  Any input is appreciated.

> > > Rich


> based on the connection. As long as you do not close the connection, the

> that if you insert into a second table with autoincrement, the first
> value will be lost, so you need to do this immediately after the insert
> that you need the AutoIncrement value from. At least this is how it
> works in SQL2000. Another user's insert would be under a different
> connection, so would not affect your value.

> Dan



Mon, 02 May 2005 13:11:17 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Insert Data Into Access Database From SQL Server ADO Insert

2. How? insert data with SQL Insert statement

3. How to Avoid the MsgBox When Using SQL Insert Into Command

4. Inserting data from an ADO recordset into a SQL Server table

5. Inserting a recordset data into a table in SQL Server

6. Trouble Inserting data into SQL Table

7. Inserting data from SQL Server into Access97 table

8. Inserting new table into database using SQL

9. INSERT INTO from SQL Server table into Access 97 table

10. Inserting Data into LONG data type field in an ORACLE Table

11. need ID from newly inserted record (altered by insert trigger on SQL server)

12. INSERT INTO SQL command

 

 
Powered by phpBB® Forum Software