Pages

Monday, March 26, 2012

Advantage database commands

Permanently delete recodes from a table
execute procedure sp_zaptable 
('T:\studies\82289\data_extract\extract.dbf')
http://devzone.advantagedatabase.com/dz/webhelp/advantage8.1/supported_statements/miscellaneous_functions.htm

Select column, data type and status from a table
select name, field_type, field_can_be_null from System.columns
where parent = 'rec_sample_test'
order by 2,1


Merge tables

MERGE web_sample_test p1 USING "//loki/web/secure/82124/cust/US/live/rec/study.add".sample p2 
ON ( p1.sam_no = p2.sam_no and p1.waveno =p2.waveno) 
WHEN MATCHED THEN 
UPDATE SET p1.surname = p2.surname, p1.forename = p2.forename 
WHEN NOT MATCHED THEN 
INSERT (sam_no, waveno, forename, surname) VALUES (p2.sam_no, p2.waveno, p2.forename, p2.surname)


Advantage database server index
http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/advantage_kwindex_static.html