USE [your_dbname]
GO

/* Creates a SQL Server 2012 table that can be used to form the input for the DSHS   */
/* batch geocoding tool.   */
/* This table meets the constraints of ArcGIS Desktop 10.3.1 for using a DBMS table  */
/* that is not registered with the geodatabase.  Load the source data into the       */
/* appropriate fields before exporting to the structured CSV file specified by the   */
/* batch geocoding tool. */

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [WAMAS_ACGC_OUT_TEMPLATE](
/* Your input information as returned from the ACG tool */
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL, --ArcGIS not null, unique ID requirement
 --Uncomment the databaseID definition that aligns best with your input data id definition
	--[inDatabaseID_shortint] [smallint] NULL, --Data type and length should be based on the ID in your database
	--[inDatabaseID_longint] [int] NULL, --Data type and length should be based on the ID in your database
	--[inDatabaseID_float] [numeric](38, 8) NULL, --Data type and length should be based on the ID in your database
	--[inDatabaseID_double] [numeric](38, 8) NULL, --Data type and length should be based on the ID in your database
	--[inDatabaseID_text] [nvarchar](200) NULL, --Data type and length should be based on the ID in your database
	--[inDatabaseID_guid] [uniqueidentifier] NULL, --Data type and length should be based on the ID in your database
	[inCompany] [nvarchar](50) NULL, --or should match your database
	[inAddress1] [nvarchar](200) NULL, --or should match your database
	[inAddress2] [nvarchar](200) NULL, --or should match your database
	[inCity] [nvarchar](50) NULL, --or should match your database
	[inState_full] [nvarchar](25) NULL, --or should match your database
	[inState_abbr] [nvarchar](2) NULL, --or should match your database
	[inZip] [nvarchar](5) NULL, --or should match your database
	[inPlus4] [nvarchar](4) NULL, --or should match your database
/* Information returned from the geocode service */
	[gcAddress1] [nvarchar](200) NULL, --varchar(100) would be the minimum recommended
	[gcCity] [nvarchar](50) NULL,
	[gcState] [nchar](2) NULL,
	[gcZip] [nchar](5) NULL,
	[gcPlus4] [nchar](4) NULL,
	[gcNumber] [nvarchar](10) NULL, --Parsed address number
	[gcPreDir] [nvarchar](10) NULL, --Parsed pre street directional
	[gcStreetName] [nvarchar](50) NULL, --Parsed street name
	[gcStreetType] [nvarchar](15) NULL, --Parsed street type
	[gcPostDir] [nvarchar](10) NULL, --Parsed post street directional
	[gcUnitNum] [nvarchar](10) NULL, --Parsed unit number
	[gcUnitType] [nvarchar](50) NULL, --Parsed unit type
	[gcXCoord] [numeric](9, 6) NULL, --Longitude referenced to WGS1984
	[gcYCoord] [numeric](9, 6) NULL, --Latitude referenced to WGS1984
	[gcLocatorName] [nvarchar](20) NULL, --The name of the locator/database that supplied the x/y coordinates
	[gcGrade] [nchar](1) NULL, --The quality of the locator used. C or better represents a full address; D or worse represents a ZIP+4, ZIP Code or city.
	[gcScore] [numeric](38, 8) NULL, --Relative score returned from within the geocoder
	[gcWAMASId] [nvarchar](20) NULL, --When available, useful to get back to the original WAMAS record. Also meets state OCIO standard for address data.  Notes:  1) this is the only required field in the Federal standard adopted by the WA OCIO into state policy.  It is described under "2.3.1.1 Address ID" in the standard.  2) As implemented for WAMAS, the value is a Base36 14 digit number encoded to a char(9) string, which is thought by the developer to have less overhead than a UUID format.  The field is defined as varchar(20) because at the present WAMAS system stage of development there is another ID that may end up being the WAMAS ID and it is defined as varchar(20).
	[gcResultCodes] [nvarchar](100) NULL, --Geocode result codes
	[gcStatusCodes] [nvarchar](500) NULL, --Geocode status codes 
	[gcErrorCodes] [nvarchar](500) NULL, --Geocode error codes
PRIMARY KEY CLUSTERED 
(
	[OBJECTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO