adding records from another table via sql

[TB]

Hi all:

I am trying to add new records to a table in Access based on content from
another table via SQL.

Since the generic SQL for adding a record is: "INSERT INTO mytable(column1,
column2) VALUES (value, value)", I thought I would be possible to do
something like this:

INSERT INTO newcustomers (customer, customergroup) values (SELECT IDcustomer
FROM customers, 7)

What I am trying to achieve here is to add new records to newcustomers,
populating the column customer with the values from IDcustomer in the table
customers and populating the column customergroup with the value 7. The
total number of records to add to newcustomers should be the equivalent of
the total number of records in customers.

However, somehow I am not getting the SQL statement right.

Any suggestions would highly appreciated.

Thanks

TB

 

[Douglas J. Steele]

If you're trying to create a table with each customer currently in the
customers table set to customergroup 7, the SQL would be:

INSERT INTO newcustomers (customer, customergroup)
SELECT IDcustomer, 7 FROM customers

 

[TB]

I am not trying to create a new table (newcustomers already exists), I only
want to add new records. 7 is not a column name in customers, its a value I
want add to the column customergroup in newcustomers.

So if for example customers contains:
[IDcustomer]
1
2
3

then I would like to add to newcustomers:
[customer], [customergroup]
1, 7
2, 7
3, 7

Thanks

TB

 

[Ken Snell [MVP]]

That is what Douglas posted for you as how to do it. 7 is a value in the SQL
statement he posted and that value of 7 will be put into the customergroup
field when you append the new records.

In a normal SQL statement, the field name just acts like a "variable" in an
equation; the actual value that is contained in the field is used, not the
name of the field. So 7 is the value and no field name is needed to tell Jet
from where to get the value 7.
--

Ken Snell
<MS ACCESS MVP>

I am not trying to create a new table (newcustomers already exists), I only
want to add new records. 7 is not a column name in customers, its a value
I want add to the column customergroup in newcustomers.

So if for example customers contains:
[IDcustomer]
1
2
3

then I would like to add to newcustomers:
[customer], [customergroup]
1, 7
2, 7
3, 7

Thanks

TB

If you're trying to create a table with each customer currently in the
customers table set to customergroup 7, the SQL would be:

INSERT INTO newcustomers (customer, customergroup)
SELECT IDcustomer, 7 FROM customers

 

[John Vinson]

I am not trying to create a new table (newcustomers already exists), I only
want to add new records. 7 is not a column name in customers, its a value I
want add to the column customergroup in newcustomers.

So if for example customers contains:
[IDcustomer]
1
2
3

then I would like to add to newcustomers:
[customer], [customergroup]
1, 7
2, 7
3, 7

That's exactly what Douglas' query will do.

There are two flavors of an INSERT query: you can insert a single
record using the VALUES operand; or you can append multiple records
using the

INSERT INTO targettable
field, field, field, field
SELECT field, field, field, field FROM sourcetable
WHERE <criteria>

The 7 is a literal value to be inserted (not a fieldname).

John W. Vinson[MVP]

 

[TB]

Thanks a lot!! it worked like a dream. Could you refer me to a place on the
web, where I can read more about this topic?

I going to bed now. It is 01:30 AM in my part of the world. Good night and
thanks again. Will check this thread again tomorrow.

TB

That is what Douglas posted for you as how to do it. 7 is a value in the
SQL statement he posted and that value of 7 will be put into the
customergroup field when you append the new records.

In a normal SQL statement, the field name just acts like a "variable" in
an equation; the actual value that is contained in the field is used, not
the name of the field. So 7 is the value and no field name is needed to
tell Jet from where to get the value 7.
--

Ken Snell
<MS ACCESS MVP>

I am not trying to create a new table (newcustomers already exists), I
only want to add new records. 7 is not a column name in customers, its a
value I want add to the column customergroup in newcustomers.

So if for example customers contains:
[IDcustomer]
1
2
3

then I would like to add to newcustomers:
[customer], [customergroup]
1, 7
2, 7
3, 7

Thanks

TB

If you're trying to create a table with each customer currently in the
customers table set to customergroup 7, the SQL would be:

INSERT INTO newcustomers (customer, customergroup)
SELECT IDcustomer, 7 FROM customers


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi all:

I am trying to add new records to a table in Access based on content
from another table via SQL.

Since the generic SQL for adding a record is: "INSERT INTO
mytable(column1, column2) VALUES (value, value)", I thought I would be
possible to do something like this:

INSERT INTO newcustomers (customer, customergroup) values (SELECT
IDcustomer FROM customers, 7)

What I am trying to achieve here is to add new records to newcustomers,
populating the column customer with the values from IDcustomer in the
table customers and populating the column customergroup with the value
7. The total number of records to add to newcustomers should be the
equivalent of the total number of records in customers.

However, somehow I am not getting the SQL statement right.

Any suggestions would highly appreciated.

Thanks

TB

 

[TB]

Thanks a lot!! it worked like a dream. Could you refer me to a place on the
web, where I can read more about this topic?

I going to bed now. It is 01:30 AM in my part of the world. Good night and
thanks again. Will check this thread again tomorrow.

TB

I am not trying to create a new table (newcustomers already exists), I
only
want to add new records. 7 is not a column name in customers, its a value
I
want add to the column customergroup in newcustomers.

So if for example customers contains:
[IDcustomer]
1
2
3

then I would like to add to newcustomers:
[customer], [customergroup]
1, 7
2, 7
3, 7

That's exactly what Douglas' query will do.

There are two flavors of an INSERT query: you can insert a single
record using the VALUES operand; or you can append multiple records
using the

INSERT INTO targettable
field, field, field, field
SELECT field, field, field, field FROM sourcetable
WHERE <criteria>

The 7 is a literal value to be inserted (not a fieldname).

John W. Vinson[MVP]