Company Switching From Micros... Help With SQL DB export

[Colin518]

Hi Guys and Gals,
As mentioned were moving from 3700 to a new POS. I would love to be able to export my existing DB if anyone has a handy SQL statement they can slide my way. I would truly be in your debt if I could also have a list of barcodes that are associated with my retail items so I can upload that info to.


As always many thanks to the people here for help.


- Colin

 

[goodbody]

This should require exporting the data of 3700 and then importing it according to your new POS data format.

GitHub - simphonydeveloper/microsblogs: micros simphony and res 3700 development information.Please give me a ⭐⭐

micros simphony and res 3700 development information.Please give me a ⭐⭐ - simphonydeveloper/microsblogs

github.com

 

[Colin518]

@goodbody Yeah that's what I'm asking help with. The SQL statements to export the necessary fields I need from 3700

 

[goodbody]

If you need anything related, you can contact me. I am here every day.
zhanglizero99@gmail.com

GitHub - simphonydeveloper/microsblogs: micros simphony and res 3700 development information.Please give me a ⭐⭐

micros simphony and res 3700 development information.Please give me a ⭐⭐ - simphonydeveloper/microsblogs

github.com

 

[_choof]

https://www.w3schools.com/sql/

honestly sql is pretty easy to figure out, there's a database schema report you can generate in autosequences and reports that gives you a list of all of the tables and columns that are in the micros db
not gonna write the code for you for free but here's a tip: you will need a join statement of some kind and will need to use seq numbers to match columns across tables. for example, if you're trying to match menu items with their prices, compare column mi_seq between the mi_def and mi_price_def tables

 

[Joseph.Hemp]

This is what I used recently for a site that used the NLU for their barcodes. They didn't use the actual barcode section in POSconfig, so I don't have one with those statements. But I hope this helps.


SELECT mid.mi_seq, mid.obj_num, name_1, name_2, maj.name as majorname, fam.name as familyname, mid.nlu_num, mid.nlu_grp, mis.name as slu1name, mis2.name as slu2name, mis3.name as slu3name, mis4.name as slu4name, mis5.name as slu5name, mis6.name as slu6name, mis7.name as slu7name, mis8.name as slu8name, mic.name as miclass,
condr.name as condreq, conda.name as condallow, condm.name as condmember, prt.name as printclass,
preset_amt_1, preset_amt_2, preset_amt_3, preset_amt_4, preset_amt_5, preset_amt_6, preset_amt_7, preset_amt_8, preset_amt_9, preset_amt_10
FROM micros.mi_def as mid
inner join micros.maj_grp_def as maj on maj.maj_grp_seq = mid.maj_grp_seq
inner join micros.fam_grp_def as fam on fam.fam_grp_seq = mid.fam_grp_seq
inner join micros.mi_price_def as mip on mip.mi_seq = mid.mi_seq
left outer join micros.mi_slu_def as mis on mis.mi_slu_seq = mid.mi_slu_seq
left outer join micros.mi_slu_def as mis2 on mis2.mi_slu_seq = mid.mi_slu2_seq
left outer join micros.mi_slu_def as mis3 on mis3.mi_slu_seq = mid.mi_slu3_seq
left outer join micros.mi_slu_def as mis4 on mis4.mi_slu_seq = mid.mi_slu4_seq
left outer join micros.mi_slu_def as mis5 on mis5.mi_slu_seq = mid.mi_slu5_seq
left outer join micros.mi_slu_def as mis6 on mis6.mi_slu_seq = mid.mi_slu6_seq
left outer join micros.mi_slu_def as mis7 on mis7.mi_slu_seq = mid.mi_slu7_seq
left outer join micros.mi_slu_def as mis8 on mis8.mi_slu_seq = mid.mi_slu8_seq

left outer join micros.mi_type_class_def as mic on mic.mi_type_seq = mid.mi_type_seq
left outer join micros.cond_grp_sel_def as condr on condr.cond_grp_sel_seq = mid.cond_req
left outer join micros.cond_grp_sel_def as conda on conda.cond_grp_sel_seq = mid.cond_allowed
left outer join micros.cond_grp_mem_def as condm on condm.cond_grp_mem_seq = mid.cond_grp_mem_seq
left outer join micros.prn_class_def as prt on prt.prn_def_class_seq = mid.prn_def_class_seq
where mip.effective_to IS NULL
order by majorname, familyname, condreq, condmember, printclass;
//output to c:\micros\nlustuff.csv;

I prefer to export it manually myself to a specific file. But if you want it to do it for you, just un-comment the last line.
Good Luck!