I need all entries from table Doc inserted into Destroyed where Destroyed_By1 is not blank. Also, I'd like to delete that entry from Doc after the insert..

Everything I've tried has failed. Any suggestions?

what table has Destroyed_By1?

Doc

try

INSERT INTO destroyed( field1, field2, field3)
SELECT field1, field2, field3
FROM DOC where Destroyed_By1<>"";

delete from DOC where Destroyed_By1<>"";

This is what I tried earlier and it didn't work.

INSERT INTO Destroyed (ID, Rack, Shelf, Stored_Date, Description, From_Date, To_Date, Destroy_Date, Placed_By1, Placed_By2, Destroy_By1, Destroy_By2)
SELECT ID, Rack, Shelf, Stored_Date, Description, From_Date, To_Date, Destroy_Date, Placed_By1, Placed_By2, Destroy_By1, Destroy_By2
FROM Doc WHERE Destroy_By1<> '';

Does it work if you just run the select? What error message do you get?

Why did it fail? What kind of error did you get? Here is what I would do in SQL:

INSERT INTO Destroyed (field1, field2)
SELECT field1, field2
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

DELETE Doc
WHERE Destroy_By1 IS NOT NULL;


OR here is some PL/SQL that will delete each record as it is entered into the new table, instead of inserting all and then deleting all...


DECLARE

CURSOR c_get_blank_recs
IS
SELECT ID, field2, field3
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

BEGIN

FOR r_get_blank_recs IN c_get_blank_recs LOOP
INSERT INTO Destroyed (ID, field2, field3)
VALUES r_get_blank_recs;

DELETE Doc WHERE ID = r_get_blank_recs.ID;
END LOOP;

COMMIT;

END;

Select runs fine.

Right now, it isn't giving me an error. Just nothing moving over into the Destroyed table.

thePITman;400273 wrote:Why did it fail? What kind of error did you get? Here is what I would do in SQL:

INSERT INTO Destroyed (field1, field2)
SELECT field1, field2
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

Good point. The field might be null and not blank. If you try running the select without the insert, you'll see exactly what it is you're trying to insert.

Keebler;400279 wrote:Select runs fine.

Right now, it isn't giving me an error. Just nothing moving over into the Destroyed table.

Does it return anything? If it does, I would think that it would insert it or give an error.

FatHobbit;400280 wrote:Good point. The field might be null and not blank. If you try running the select without the insert, you'll see exactly what it is you're trying to insert.

Technically, the two comparisons of <> '' and IS NOT NULL are the same, but depending on the data type, <> '' may not be acceptable. In almost all cases I go with the IS [NOT] NULL instead of <> [or =] ''.

If Keebler can execute an anonymous PL/SQL block, it'd be easy.

FatHobbit;400286 wrote:Does it return anything? If it does, I would think that it would insert it or give an error.

Agreed.

Fuckin' a. I got it. It is correct. Fucking Office 2007 and its security content fucking AGAIN! How many times do I have to enable this shit?

<> '' worked.

Keebler;400294 wrote:Fuckin' a. I got it. It is correct. Fucking Office 2007 and its security content fucking AGAIN! How many times do I have to enable this shit?

Keebler;400295 wrote:<> '' worked.

Haha! Congrats again. Document it. Self-help (tier 0 support) is always the best and quickest option... but we don't mind, either!

My problem is I get it right early on but forget about the content blocker thing in Access 2007 and I think its wrong so I try everything else. LOL

Good deal.