formatsurveydata.sql

if object_id('dbo.BRFSSsurvey2013') is not null
drop table dbo.BRFSSsurvey2013;

SELECT [_STATE]
      -- ,[filler1]
      ,[FMONTH]
      ,[IMONTH]
      ,[IDAY]
      ,[IYEAR]
      -- ,[filler2]
      ,[DISPCODE]
      ,[SEQNO]
      -- ,[filler3]
      ,[CTELENUM]
      ,[PVTRESD1]
      ,[COLGHOUS]
      ,[STATERES]
      ,[CELLFON3]
      ,[LADULT]
      ,[NUMADULT]
      ,[NUMMEN]
      ,[NUMWOMEN]
      -- ,[filler4]
      ,[GENHLTH]
      ,[PHYSHLTH]
      ,[MENTHLTH]
      ,[POORHLTH]
      ,[HLTHPLN1]
      ,[PERSDOC2]
      ,[MEDCOST]
      ,[CHECKUP1]
      ,[SLEPTIM1]
      ,[BPHIGH4]
      ,[BPMEDS]
      ,[BLOODCHO]
      ,[CHOLCHK]
      ,[TOLDHI2]
      ,[CVDINFR4]
      ,[CVDCRHD4]
      ,[CVDSTRK3]
      ,[ASTHMA3]
      ,[ASTHNOW]
      ,[CHCSCNCR]
      ,[CHCOCNCR]
      ,[CHCCOPD1]
      ,[HAVARTH3]
      ,[ADDEPEV2]
      ,[CHCKIDNY]
      ,[DIABETE3]
      -- ,[filler5]
      ,[VETERAN3]
      ,[MARITAL]
      ,[CHILDREN]
      ,[EDUCA]
      ,[EMPLOY1]
      ,[INCOME2]
      ,[WEIGHT2]
      ,[HEIGHT3]
      -- ,[filler6]
      ,[NUMHHOL2]
      ,[NUMPHON2]
      ,[CPDEMO1]
      ,[CPDEMO4]
      ,[INTERNET]
      ,[RENTHOM1]
      ,[SEX]
      ,[PREGNANT]
      ,[QLACTLM2]
      ,[USEEQUIP]
      ,[BLIND]
      ,[DECIDE]
      ,[DIFFWALK]
      ,[DIFFDRES]
      ,[DIFFALON]
      ,[SMOKE100]
      ,[SMOKDAY2]
      ,[STOPSMK2]
      ,[LASTSMK2]
      ,[USENOW3]
      ,[ALCDAY5]
      ,[AVEDRNK2]
      ,[DRNK3GE5]
      ,[MAXDRNKS]
      ,[FRUITJU1]
      ,[FRUIT1]
      ,[FVBEANS]
      ,[FVGREEN]
      ,[FVORANG]
      ,[VEGETAB1]
      ,[EXERANY2]
      ,[EXRACT11]
      ,[EXEROFT1]
      ,[EXERHMM1]
      ,[EXRACT21]
      ,[EXEROFT2]
      ,[EXERHMM2]
      ,[STRENGTH]
      ,[LMTJOIN3]
      ,[ARTHDIS2]
      ,[ARTHSOCL]
      ,[JOINPAIN]
      ,[SEATBELT]
      ,[FLUSHOT6]
      ,[FLSHTMY2]
      ,[TETANUS]
      ,[PNEUVAC3]
      ,[HIVTST6]
      ,[HIVTSTD3]
      ,[WHRTST10]
      -- ,[filler7]
      ,[PDIABTST]
      ,[PREDIAB1]
      ,[DIABAGE2]
      ,[INSULIN]
      ,[BLDSUGAR]
      ,[FEETCHK2]
      ,[DOCTDIAB]
      ,[CHKHEMO3]
      ,[FEETCHK]
      ,[EYEEXAM]
      ,[DIABEYE]
      ,[DIABEDU]
      ,[PAINACT2]
      ,[QLMENTL2]
      ,[QLSTRES2]
      ,[QLHLTH2]
      ,[MEDICARE]
      ,[HLTHCVRG]
      ,[DELAYMED]
      ,[DLYOTHER]
      ,[NOCOV121]
      ,[LSTCOVRG]
      ,[DRVISITS]
      ,[MEDSCOST]
      ,[CARERCVD]
      ,[MEDBILLS]
      ,[SSBSUGAR]
      ,[SSBFRUT2]
      ,[WTCHSALT]
      ,[LONGWTCH]
      ,[DRADVISE]
      ,[ASTHMAGE]
      ,[ASATTACK]
      ,[ASERVIST]
      ,[ASDRVIST]
      ,[ASRCHKUP]
      ,[ASACTLIM]
      ,[ASYMPTOM]
      ,[ASNOSLEP]
      ,[ASTHMED3]
      ,[ASINHALR]
      ,[HAREHAB1]
      ,[STREHAB1]
      ,[CVDASPRN]
      ,[ASPUNSAF]
      ,[RLIVPAIN]
      ,[RDUCHART]
      ,[RDUCSTRK]
      ,[ARTTODAY]
      ,[ARTHWGT]
      ,[ARTHEXER]
      ,[ARTHEDU]
      ,[IMFVPLAC]
      ,[HPVADVC2]
      ,[HPVADSHT]
      ,[HADMAM]
      ,[HOWLONG]
      ,[PROFEXAM]
      ,[LENGEXAM]
      ,[HADPAP2]
      ,[LASTPAP2]
      ,[HADHYST2]
      ,[BLDSTOOL]
      ,[LSTBLDS3]
      ,[HADSIGM3]
      ,[HADSGCO1]
      ,[LASTSIG3]
      ,[PCPSAAD2]
      ,[PCPSADI1]
      ,[PCPSARE1]
      ,[PSATEST1]
      ,[PSATIME]
      ,[PCPSARS1]
      ,[PCPSADE1]
      ,[PCDMDECN]
      ,[RRCLASS2]
      ,[RRCOGNT2]
      ,[RRATWRK2]
      ,[RRHCARE3]
      ,[RRPHYSM2]
      ,[RREMTSM2]
      ,[MISNERVS]
      ,[MISHOPLS]
      ,[MISRSTLS]
      ,[MISDEPRD]
      ,[MISEFFRT]
      ,[MISWTLES]
      ,[MISNOWRK]
      ,[MISTMNT]
      ,[MISTRHLP]
      ,[MISPHLPF]
      -- ,[filler8]
      ,[SCNTMONY]
      ,[SCNTMEAL]
      ,[SCNTPAID]
      ,[SCNTWRK1]
      ,[SCNTLPAD]
      ,[SCNTLWK1]
      ,[SCNTVOT1]
      -- ,[filler9]
      ,[RCSGENDR]
      -- ,[filler10]
      ,[RCSRLTN2]
      ,[CASTHDX2]
      ,[CASTHNO2]
      ,[EMTSUPRT]
      ,[LSATISFY]
      -- ,[filler11]
      ,[CTELNUM1]
      ,[CELLFON2]
      ,[CADULT]
      ,[PVTRESD2]
      ,[CCLGHOUS]
      ,[CSTATE]
      -- ,[filler12]
      ,[LANDLINE]
      ,[PCTCELL]
      ,[QSTVER]
      ,[QSTLANG]
      -- ,[filler13]
      ,[MSCODE]
      ,[_STSTR]
      ,[_STRWT]
      -- ,[filler14]
      ,[_RAWRAKE]
      ,[_WT2RAKE]
      -- ,[filler15]
      ,[_IMPRACE]
      ,[_IMPNPH]
      -- ,[filler16]
      ,[_IMPEDUC]
      ,[_IMPMRTL]
      ,[_IMPHOME]
      -- ,[filler17]
      ,[_CHISPNC]
      -- ,[filler18]
      ,[_CRACE1]
      ,[_IMPCAGE]
      ,[_IMPCRAC]
      ,[_IMPCSEX]
      -- ,[filler19]
      ,[_CLLCPWT]
      -- ,[filler20]
      ,[_DUALUSE]
      ,[_DUALCOR]
      ,[_LLCPWT2]
      -- ,[filler21]
      ,[_LLCPWT]
      -- ,[filler22]
      ,[_RFHLTH]
      ,[_HCVU651]
      ,[_RFHYPE5]
      ,[_CHOLCHK]
      ,[_RFCHOL]
      ,[_LTASTH1]
      ,[_CASTHM1]
      ,[_ASTHMS1]
      ,[_DRDXAR1]
      -- ,[filler23]
      ,[_PRACE1]
      ,[_MRACE1]
      -- ,[filler24]
      ,[_HISPANC]
      ,[_RACE]
      ,[_RACEG21]
      ,[_RACEGR3]
      ,[_RACE_G1]
      ,[_AGEG5YR]
      ,[_AGE65YR]
      ,[_AGE_G]
      ,[HTIN4]
      ,[HTM4]
      ,[WTKG3]
      ,[_BMI5]
      ,[_BMI5CAT]
      ,[_RFBMI5]
      ,[_CHLDCNT]
      ,[_EDUCAG]
      ,[_INCOMG]
      ,[_SMOKER3]
      ,[_RFSMOK3]
      ,[DRNKANY5]
      ,[DROCDY3_]
      ,[_RFBING5]
      ,[_DRNKDY4]
      ,[_DRNKMO4]
      ,[_RFDRHV4]
      ,[_RFDRMN4]
      ,[_RFDRWM4]
      ,[FTJUDA1_]
      ,[FRUTDA1_]
      ,[BEANDAY_]
      ,[GRENDAY_]
      ,[ORNGDAY_]
      ,[VEGEDA1_]
      ,[_MISFRTN]
      ,[_MISVEGN]
      ,[_FRTRESP]
      ,[_VEGRESP]
      ,[_FRUTSUM]
      ,[_VEGESUM]
      ,[_FRTLT1]
      ,[_VEGLT1]
      ,[_FRT16]
      ,[_VEG23]
      ,[_FRUITEX]
      ,[_VEGETEX]
      ,[_FRTLT1_2]
      ,[_VEGLT1_2]
      ,[_TOTINDA]
      ,[METVL11_]
      ,[METVL21_]
      ,[MAXVO2_]
      ,[FC60_]
      ,[ACTIN11_]
      ,[ACTIN21_]
      ,[PADUR1_]
      ,[PADUR2_]
      ,[PAFREQ1_]
      ,[PAFREQ2_]
      ,[_MINAC11]
      ,[_MINAC21]
      ,[STRFREQ_]
      ,[PAMISS1_]
      ,[PAMIN11_]
      ,[PAMIN21_]
      ,[PA1MIN_]
      ,[PAVIG11_]
      ,[PAVIG21_]
      ,[PA1VIGM_]
      ,[_PACAT1]
      ,[_PAINDX1]
      ,[_PA150R2]
      ,[_PA300R2]
      ,[_PA30021]
      ,[_PASTRNG]
      ,[_PAREC1]
      ,[_PASTAE1]
      ,[_LMTACT1]
      ,[_LMTWRK1]
      ,[_LMTSCL1]
      ,[_RFSEAT2]
      ,[_RFSEAT3]
      ,[_FLSHOT6]
      ,[_PNEUMO2]
      ,[_AIDTST3]
      ,[_AGE80]
  into dbo.BRFSSsurvey2013
  FROM [dbo].[import_combinedsurvey_2013];
  
  alter table dbo.BRFSSsurvey2013
  add constraint [brfss13_pk] PRIMARY KEY CLUSTERED 
(
	[_STATE] ASC,
	[IYEAR] ASC,
	[IMONTH] ASC,
	[IDAY] ASC,
	[SEQNO] ASC
);