Using union in INSERT statement

[dswitzer]

I have a VB app hitting a MSAccess DB.

Currently, I run a series of SQL statements against the DB whenever there is an INSERT needed:

INSERT INTO tblRepData (fname,lname,unit,date,metric,value)
SELECT 'Julie' , 'Carroll' , 'Stop Payment' , '1/1/2001' , 'Accts Worked' , '3' 

INSERT INTO tblRepData (fname,lname,unit,date,metric,value)
SELECT 'Bob','Smith','Stop Payment','1/1/2001','Accts Worked','7'

Is there a way to union these together so I only hit the DB once (instead of having a statement for every record)?

Something like :

INSERT INTO tblRepData (fname,lname,unit,date,metric,value)
SELECT 'Julie' , 'Carroll' , 'Stop Payment' , '1/1/2001' , 'Accts Worked' , '3' 
union all
SELECT 'Bob','Smith','Stop Payment','1/1/2001','Accts Worked','7'

Seems like it should work as the individual SELECTS work -- but Access keeps asking for a table.

Even if I forgo the INSERT and just run the union -- it still asks for a table:

SELECT 'Julie' , 'Carroll' , 'Stop Payment' , '1/1/2001' , 'Accts Worked' , '3' 
union all
SELECT 'Bob','Smith','Stop Payment','1/1/2001','Accts Worked','7'

Any ideas?

 

[PHV]

Why not playing with Recordset and AddNew method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dswitzer]

Hmm....it worked in SQL Server....why not MSAccess?

Well, OK, I can get it in Access by doing some funky stuff.

I added a new table called dummyTable and gave it a single record(data type/title don't matter -- single record does matter or you get dupes).

tblRepData (fname,lname,unit,[date],metric,[value])

select * from (SELECT 'Julie' as fname, 'Carroll' as lname, 'Stop Payment' as unit, '1/1/2001' as [date] , 'Accts Worked' as metric , '3'  as [value]
 FROM dummyTable  
union 
SELECT 'Bob' as fname, 'Smith' as lname, 'Stop Payment' as unit, '1/1/2001' as [date] , 'Accts Worked' as metric , '3'  as [value]
FROM dummyTable);

This worked...but if anyone has a cleaner way - I'd love to see it!