[RESOLVED] SQL Help

[manofsteel00]

I'm trying to write an SQL expression so that I can pull all the data from the TEST table into the MAIN table where the Product number (PROD) of Test is not in the ALTER table. Basically I'm trying to do an INSERT INTO nested query. Here is the code I have so far and it's not working:

INSERT INTO [MAIN] ([PICKSL], [SLOT], [RES],[PROD],[DESC],[PACK],[MANU ID],[HITIX],[PALLET ID],[PALLET QTY],[DFP]) ;
SELECT t.PICKSL, t.SLOT, t.RES, t.PROD, t.DESC, t.PACK, t.[MANU ID], t.HITIX, t.[PALLET ID], t.[PALLET QTY], t.DFP
FROM Test AS t;
WHERE (((Exists (SELECT * 
  FROM [ALTER] a 
  WHERE  a.PROD = t.PROD 
   ))=False));

Any help would be greatly appreciated.

[Shuja Ali]

DESC is a resevre word in SQL so you should put it in square brackets as well. And why do you have semi-colos at the end. If it is a one query then there shouldn't be any semi-colons in between.

Try this

INSERT INTO 
	[MAIN] ([PICKSL], [SLOT], [RES],[PROD],[DESC],[PACK],
	[MANU ID],[HITIX],[PALLET ID],[PALLET QTY],[DFP])	
	SELECT 
		t.PICKSL, t.SLOT, t.RES, t.PROD, t.[DESC], t.PACK, t.[MANU ID], 
		t.HITIX, t.[PALLET ID], t.[PALLET QTY], t.DFP
	FROM Test AS t WHERE Not Exists (SELECT *  FROM [ALTER] a WHERE  a.PROD = t.PROD)

[si_the_geek]

Why are there semi-colons inside the SQL statement? (before the Select and Where)

Your Where clause could be written a bit more cleanly:

WHERE Not Exists (SELECT * 
  FROM [ALTER] a 
  WHERE  a.PROD = t.PROD 
   );

However, whether this format is the correct method for Insert/Select depends on which DBMS you are using.

[manofsteel00]

I am using MS Access, I have made the changes per Shuja Ali and si the geek; however, the data in the TEST table is still not being inserted into the MAIN table. Any other suggestions?

[Shuja Ali]

It all depends on whether this condition is evavluating to true or false.

WHERE Not Exists (SELECT *  FROM [ALTER] a WHERE  a.PROD = t.PROD)

Try running just this part and see if it returns any rows

SELECT 
		t.PICKSL, t.SLOT, t.RES, t.PROD, t.[DESC], t.PACK, t.[MANU ID], 
		t.HITIX, t.[PALLET ID], t.[PALLET QTY], t.DFP
	FROM Test AS t WHERE Not Exists (SELECT *  FROM [ALTER] a WHERE  a.PROD = t.PROD)

And are you getting any error message or is it just that data is bnot getting inserted.

[manofsteel00]

No I'm not getting any error message; it appears to work; however, nothing is being inserted into the MAIN table. I did try just the select statement and if retrieved information. Any other suggestions? Thanks for your time.

[Shuja Ali]

No I'm not getting any error message; it appears to work; however, nothing is being inserted into the MAIN table. I did try just the select statement and if retrieved information. Any other suggestions? Thanks for your time.

Does it show 0 Rows Inserted or any other messagebox?

Can you post what are you using again? Make sure there are no semicolons in the query, just one semi-colon at the end or else it will not insert anything.

[manofsteel00]

There is no messagebox statement. I am testing it in Access itself as a query. It generates information as a query but never inserts the information into the MAIN table. The code I am using is

INSERT INTO
[MAIN] ([PICKSL], [SLOT], [RES],[PROD],[DESC],[PACK],
[MANU ID],[HITIX],[PALLET ID],[PALLET QTY],[DFP])
SELECT
t.PICKSL, t.SLOT, t.RES, t.PROD, t.[DESC], t.PACK, t.[MANU ID],
t.HITIX, t.[PALLET ID], t.[PALLET QTY], t.DFP
FROM Test AS t WHERE Not Exists (SELECT * FROM [ALTER] a WHERE a.PROD = t.PROD)

[manofsteel00]

Found the problem: I saved the query and it worked fine. My bad. Thanks for all your help though.