SQL: INSERT INTO with VALUES *and* SELECT

[waldemar]

Via INSERT INTO .... SELECT ... WHERE .... I managed to fill in missing entries in a table. Now I would like to fill in some other fields of those newly created entries - at the same time.... kind of a combination of SELECT *and* VALUES ()...

Does anybody know how to accomplish this?

This is how far I got:

INSERT INTO tblB
SELECT [tblA].[ID] AS ID
FROM tblA
WHERE [ID] NOT IN (SELECT [ID] FROM [tblB]);

(I imagine some addition like ", [strVariable]='Defaultvalue'&quot

 

[Quehay]

Once you've got rows in you're now longer SELECTing INTO (append), but rather UPDATEing. Access makes this easy by doing bulk updates (you don't have to iterate through a cursor and update by row).

This should work:
[tt]
UPDATE tblB
INNER JOIN tblA ON tblB.ID = tblA.ID
SET tblB.fld1 = tblA.fld1, tblB.fld2 = tblA.fld2, etc.

[/tt]

 

[waldemar]

You mean I have to split this procedure up into two separate queries? But this brings up a new problem:

In the second query I do not know which entries were newly created in the first query. I can not UPDATE all entries since this overwrites all existing values then...

 

[Quehay]

Terry Broadbent posted a solution to this previously, but I don't know where it is...

What you need is an UPDATE query based on an OUTER JOIN, which will add records that are found in the target table and update fields where the records are found.

Look at this MS KB article: Q208870

 

[waldemar]

nonono I need a query which will add records that are NOT found in another table and update fields of entries that are just added...

 

[Quehay]

Then you'll need a way to identify imported records.

 

[waldemar]

In Theory the Query would probably look like this:

INSERT INTO tblB ([ID], [strVariable1], [strVariable2]) SELECT [tblA].[ID] AS ID FROM tblA WHERE [ID] NOT IN (SELECT [ID] FROM [tblB]), 'Value1', 'Value2';

Doesn't work unfortunately....

 

[waldemar]

With help from somebody else.... THIS IS IT, in case somebody is interested:

INSERT INTO tblB
SELECT [tblA].[ID] AS ID, 'Value1' AS [Field1], 'Value2' AS [Field2]
FROM tblA
WHERE [ID] NOT IN (SELECT [ID] FROM [tblB]);