Please see attached text document.
Thanks,
Dave
David Dabney
OHH Database Specialist/Programmer
Hollings Marine Lab
131 Fort Johnson Road
Charleston, SC 29464
843.762.8984
insert into public_collection (original_collection_code, method, station_id, date_time, sampling_project_id,
contributor)
select LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id, Min(LUCES_wq.datetime) AS MinOfdatetime, 12, 1
FROM LUCES_wq
GROUP BY LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id
ORDER BY LUCES_wq.deploy_code
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per,
do_mg_l,depth_m )
SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m
FROM LUCES_wq as lwq
INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per,
do_mg_l,depth_m)
SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m
FROM LUCES_wq as lwq
INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code
WHERE c.id <=1980
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per,
do_mg_l,depth_m)
SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m
FROM LUCES_wq as lwq
INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code
WHERE c.id <=1981
and c.id >1980
TEST IN EMS
insert into public.wq (collection_id, date_time, original_deployment_code, water_temp, depth_m, salinity, sp_cond,
do_per,do_mg_l, ph) values (1982,'4/1/2001 3:00:00','MLM20010327',17.67,1.64,30.20,46.40,72.80,5.84,7.61)
psql
COPY wq ( collection_id , date_time , original_deployment_code , water_temp , depth_m , salinity , sp_cond , do_per ,
do_mg_l, ph ) FROM '/usr/local/pgsql/ddluces.csv' CSV;
COPY
ohh_v8=#
Deleted inserted records and tried to do in Access with ' ' around datetime. Access gives type mismatch. Tried
queryw/out. Now it works.
Should we always concatenate to insert date_time?
Now all the sudden this works.
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per,
do_mg_l,depth_m)
SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m
FROM LUCES_wq as lwq
INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code
WHERE c.id >=1982
