Insert & Query Query Question

[Mike Charney]

I have a two part question:

First I want to insert data into a table and I am using the following
command:

INSERT INTO tblmain
SELECT field1, field2, etc...
FROM tblimport
WHERE ??????
The where clause is the part I am having trouble with. I want to only insert
the rows where field F1 one doesn't exist in table tblmain. What would the
where clause look like? I am using MS-Access to transfer the data from a
flat file to a SQL data base because I do not know that much SQL
programming.
Second part of the question is that after the data is inserted I want to
pull out a field in tblmain (pat_id which is only generated when a new line
of data is inserted into tblmain) and place the value in the table tblimport
(in field pat_id) but only where fields F1 match in both tables.

I don't want to find the pat_id when I insert data because there will be
cases when the data in the tblimport is already in the tblmain and I don't
want to change the data in the tblmain.

Does that make any sense? It sounded ok in my head...

Thanks in advance...

Mike Charney

[Rich P]

I will try to help you out with part 1 for starters.

First I want to insert data into a table and I am using the following
command:

INSERT INTO tblmain
SELECT field1, field2, etc...
FROM tblimport
WHERE ??????
The where clause is the part I am having trouble with. I want to only
insert the rows where field F1 one doesn't exist in table tblmain. What
would the where clause look like? I am using MS-Access to transfer the
data from a
flat file to a SQL data base because I do not know that much SQL
programming.
<<

It sounds like you created a table and did not assign any column names,
so Access is using default column names of F1, F2, F3,... If this is
the case, then I would start out by first entering the respective column
names in your tblMain, otherwise you will be spinning you wheels for a
while trying to get the fields straight.

Next, you state that you want to insert rows into tblMain where field F1
doesn't exist. Do you mean where field F1 is null in tblMain? If this
is what you mean then it sounds like what you really want to do is to
update rows that already exist in tblMain. Else, if you mean that field
F1 in tblImport Is Null, then just specify

Insert Into tblMain select * from tblImport where F1 Is Null

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Mike Charney]

"Rich P" <rp*****@aol.com> wrote in message
news:GV***************@news.uswest.net...

I will try to help you out with part 1 for starters.

First I want to insert data into a table and I am using the following
command:

INSERT INTO tblmain
SELECT field1, field2, etc...
FROM tblimport
WHERE ??????
The where clause is the part I am having trouble with. I want to only
insert the rows where field F1 one doesn't exist in table tblmain. What
would the where clause look like? I am using MS-Access to transfer the
data from a
flat file to a SQL data base because I do not know that much SQL
programming.
<<

It sounds like you created a table and did not assign any column names,
so Access is using default column names of F1, F2, F3,... If this is
the case, then I would start out by first entering the respective column
names in your tblMain, otherwise you will be spinning you wheels for a
while trying to get the fields straight.

Next, you state that you want to insert rows into tblMain where field F1
doesn't exist. Do you mean where field F1 is null in tblMain? If this
is what you mean then it sounds like what you really want to do is to
update rows that already exist in tblMain. Else, if you mean that field
F1 in tblImport Is Null, then just specify

Insert Into tblMain select * from tblImport where F1 Is Null

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich,

Not exactly. I am importing data to a table (tblimport) and then inserting
the rows that don't already exist in to tblmain. As for the column names, I
was reading some web pages and just came across that. I will deal with that
right now but I still don't know how to write the SQL insert statement.

Thanks for the help.

[Rich P]

OK. It sounds like tblImport and tblMain have the same structure.
Going with this idea, I hope your tables have a key field that uniquely
identifies each row (or composite key fields). If so, you can do this:

Insert Into tblMain Select * from tblImport t1 left Join tblMain t2 On
t1.RecordID = t2.RecordID Where t2.RecordID Is Null

This query will pick up all the records from tblImport which are
currently not in tblMain - based on the key field of RecordID --> where
t1.RecordID Is Null.
Rich

*** Sent via Developersdex http://www.developersdex.com ***