append query

[subs]

i have a frtbill table in a new database called as db1. i get this
database from an external organization. I want to append this frtbill
table into another table also called as frtbill in my existing
database SunUSA. Is there any query that i can run which can do this
append operation. I donot want the users to go into design view and do
append operations. is there a sql query which can do the append .Thanks

 

[tina]

yes, you can run an Append query. and you can do it without actually linking
the table from the other db into your SunUSA db. see the following syntax,
as

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill;

the first reference to table frtbill refers to the local table in SunUSA db.
inside the parentheses, list each field that will receive data from the
outside table, and note the spaces between the first/last field names and
each parens. in the SELECT section of the statement, list each corresponding
field from the outside table that will dump into the local table. note that
the field names between the two tables do not have to match, neither do the
table names for that matter. just keep in mind that the fields will match up
in order - the first field listed from the local table will receive the data
from the first field listed from the outside table, 2nd field local gets
data from 2nd field outside table, etc. inside the FROM section brackets,
after the = sign, include the full filepath to the outside database. if you
need to append only certain records from the outside table into the local
table, add a WHERE clause on the end; for example:

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill WHERE Field2 = "x";

hth

 

[subs]

yes, you can run an Append query. and you can do it without actually linking
the table from the other db into your SunUSA db. see the following syntax,
as

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill;

the first reference to table frtbill refers to the local table in SunUSA db.
inside the parentheses, list each field that will receive data from the
outside table, and note the spaces between the first/last field names and
each parens. in the SELECT section of the statement, list each corresponding
field from the outside table that will dump into the local table. note that
the field names between the two tables do not have to match, neither do the
table names for that matter. just keep in mind that the fields will matchup
in order - the first field listed from the local table will receive the data
from the first field listed from the outside table, 2nd field local gets
data from 2nd field outside table, etc. inside the FROM section brackets,
after the = sign, include the full filepath to the outside database. ifyou
need to append only certain records from the outside table into the local
table, add a WHERE clause on the end; for example:

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill WHERE Field2 = "x";

hth

i have a frtbill table in  a new database called as db1. i get this
database from an external organization. I want to append this frtbill
table into another table also called as frtbill in my existing
database SunUSA. Is there any query that i can run which can do this
append operation. I donot want the users to go into design view and do
append operations. is there a sql query which can do the append .Thanks- Hide quoted text -

- Show quoted text -

hi

thanks for your help- i will try this. but both my tables have equal
no of columns which is 22 columns. So do i have to list all columns or
is there a short cut like * . Pls let me know. Thanks

 

[tina]

nope, no shortcut. the only thing you might do is link the outside table
into your local db temporarily, then build the Append query in the query
design grid. if the fields in the two tables have the same name, then do it
like this: open a new query to Design view, choosing the linked table from
the Show Table list that pops up. do NOT add any fields to the grid. from
the menu bar, select Query | Append, and choose the local table in the
Append dialog. *now*, add the fields to the design grid by highlighting all
of them in the table field list above the grid, dragging them into the grid,
and dropping them. make sure you do NOT include the * at the top of the
field list. the fields in the local table that match fieldnames in the
outside table should automatically be filled in, in the design grid. check
for any missing fieldnames, and add them yourself.

if you want to keep the outside table linked into your local database, save
the query and you're done. if instead you want to run the query without a
table link, using the syntax i posted previously, then save the query, and
open the SQL pane, and add in the filepath surrounded by brackets, then save
and close. delete the linked table, compact the db, and you're done.

hth

yes, you can run an Append query. and you can do it without actually linking
the table from the other db into your SunUSA db. see the following syntax,
as

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill;

the first reference to table frtbill refers to the local table in SunUSA db.
inside the parentheses, list each field that will receive data from the
outside table, and note the spaces between the first/last field names and
each parens. in the SELECT section of the statement, list each corresponding
field from the outside table that will dump into the local table. note that
the field names between the two tables do not have to match, neither do the
table names for that matter. just keep in mind that the fields will match up
in order - the first field listed from the local table will receive the data
from the first field listed from the outside table, 2nd field local gets
data from 2nd field outside table, etc. inside the FROM section brackets,
after the = sign, include the full filepath to the outside database. if you
need to append only certain records from the outside table into the local
table, add a WHERE clause on the end; for example:

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill WHERE Field2 = "x";

hth

i have a frtbill table in a new database called as db1. i get this
database from an external organization. I want to append this frtbill
table into another table also called as frtbill in my existing
database SunUSA. Is there any query that i can run which can do this
append operation. I donot want the users to go into design view and do
append operations. is there a sql query which can do the append .Thanks-

Hide quoted text -

- Show quoted text -

hi

thanks for your help- i will try this. but both my tables have equal
no of columns which is 22 columns. So do i have to list all columns or
is there a short cut like * . Pls let me know. Thanks

 

[subs]

nope, no shortcut. the only thing you might do is link the outside table
into your local db temporarily, then build the Append query in the query
design grid. if the fields in the two tables have the same name, then do it
like this:  open a new query to Design view, choosing the linked table from
the Show Table list that pops up. do NOT add any fields to the grid. from
the menu bar, select Query | Append, and choose the local table in the
Append dialog. *now*, add the fields to the design grid by highlighting all
of them in the table field list above the grid, dragging them into the grid,
and dropping them. make sure you do NOT include the * at the top of the
field list. the fields in the local table that match fieldnames in the
outside table should automatically be filled in, in the design grid. check
for any missing fieldnames, and add them yourself.

if you want to keep the outside table linked into your local database, save
the query and you're done. if instead you want to run the query without a
table link, using the syntax i posted previously, then save the query, and
open the SQL pane, and add in the filepath surrounded by brackets, then save
and close. delete the linked table, compact the db, and you're done.

hth

yes, you can run an Append query. and you can do it without actually linking
the table from the other db into your SunUSA db. see the following syntax,
as

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill;

the first reference to table frtbill refers to the local table in SunUSA db.
inside the parentheses, list each field that will receive data from the
outside table, and note the spaces between the first/last field names and
each parens. in the SELECT section of the statement, list each corresponding
field from the outside table that will dump into the local table. note that
the field names between the two tables do not have to match, neither do the
table names for that matter. just keep in mind that the fields will match up
in order - the first field listed from the local table will receive the data
from the first field listed from the outside table, 2nd field local gets
data from 2nd field outside table, etc. inside the FROM section brackets,
after the = sign, include the full filepath to the outside database. if you
need to append only certain records from the outside table into the local
table, add a WHERE clause on the end; for example:

INSERT into frtbill ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3
FROM [;Database=C:\FolderName\db1.mdb].frtbill WHERE Field2 = "x";

hth

news:[email protected]...

i have a frtbill table in a new database called as db1. i get this
database from an external organization. I want to append this frtbill
table into another table also called as frtbill in my existing
database SunUSA. Is there any query that i can run which can do this
append operation. I donot want the users to go into design view and do
append operations. is there a sql query which can do the append .Thanks-

Hide quoted text -

- Show quoted text -

hi

thanks for your help- i will try this. but both my tables have equal
no of columns which is 22 columns. So do i have to list all columns or
is there a short cut like * . Pls let me know. Thanks- Hide quoted text -

- Show quoted text -

Thanks once again. but in the second method of linking the tables, you
have to export the outside table into the current database before
appending it. Right? If that is the case, the user does know how to
export the file into access database.

 

[tina]

not sure what you mean by "second method". you can link the outside table
into your local db to simplify creating the query, as i described
previously. you can either leave it linked, and just run the query as you
created it in the query Design grid, OR you can delete the link, modify the
query's SQL statement to use the outside table directly at runtime, again,
as i described previously. if you modify the SQL, you don't need to link the
outside table in the database window; the link occurs whenever the query
runs.

hth



Thanks once again. but in the second method of linking the tables, you
have to export the outside table into the current database before
appending it. Right? If that is the case, the user does know how to
export the file into access database.