MSSQL_IMPORT table design
The table below is a list of the fields in the temporary table MSSQL_IMPORT, which is used while importing data from spreadsheets into the MBARI_Samples database. These field names must appear as columns in the spreadsheet from which the data is to be imported. The column names must match the spelling of the field names. The data in the columns must be of the proper format for the corresponding database fields; data types are suggested here, but this representation only scratches the surface (e.g., SeedConcepts must be formatted as in the knowledge base; TapeTimeCode must be as 00:00:00:00; others, such as PreservativeMethod, need to match the values in the lookup tables). Familiarity with the database is essential. See the Sample Coordinator or other members of the development team for assistance.
| Field Name | Data Type (SQL Server) |
Field Size | Notes |
| StartDTG | smalldatetime | 4 | Primary key, required |
| CollectionRefName | varchar | 20 | Primary key, required |
| SampleRefName | varchar | 20 | Primary key, required |
| ParentRefName | varchar | 20 | |
| SubsamplePosition | varchar | 50 | |
| DiveName | varchar | 15 | |
| Yearday | float | 8 | Not imported |
| CollectionEventDTG | datetime | 8 | Required |
| TapeTimeCode | varchar | 10 | Use "text" formatting in Excel spreadsheet |
| Depth | real | 5 | Number (not a formula) |
| Latitude | real | 4 | Use "text" formatting in Excel spreadsheet |
| Longitude | real | 4 | Use "text" formatting in Excel spreadsheet |
| CollectionVIMSConcept | varchar | 30 | Use knowledge base formatting |
| SeedConcept1 | varchar | 30 | Use knowledge base formatting |
| SeedConcept2 | varchar | 30 | Use knowledge base formatting |
| SeedConcept3 | varchar | 30 | Use knowledge base formatting |
| SeedConcept4 | varchar | 30 | Use knowledge base formatting |
| WaypointName | varchar | 40 | |
| Equipment | varchar | 25 | Value must be in Equipment table; use knowledge base formatting |
| Comment | varchar | 255 | |
| DescriptionComment | varchar | 255 | |
| ChiefScientist | varchar | 20 | Last name only; value must be in Person table |
| Collector | varchar | 20 | Last name only; value must be in Person table |
| Contact | varchar | 20 | Last name only; value must be in Person table |
| ShipName | varchar | 25 | Not required, but used by script to create unique CollectionGroup records |
| ROVName | varchar | 20 | |
| LotCount | int | 4 | |
| Sorted | bit | 1 | Yes/No required |
| PreservativeMethod | varchar | 20 | Value must be in PreservativeMethod table |
| FacilityName | varchar | 30 | Value must be in Facility table |
| SpecificLocationDescription | varchar | 50 | |
| MaterialName | varchar | 25 | Value must be in Material table |
| SubSampleMethod | varchar | 25 | Value must be in SubSampleMethod table |
| AnalysisComment | varchar | 255 | |
| SampleExists | bit | 1 | Yes/No required |
| CitationID | int | 4 | Value must be in Citation table |
| ContainerType | varchar | 25 | Value must be in ContainerType table |
| Concentration | varchar | 10 | |
| Quantity | varchar | 5 | |
| Units | varchar | 10 | |
| Owner | varchar | 35 | |
| ConditionComment | varchar | 255 | |
| NeedsLabel | bit | 1 | Yes/No required |
| NeedsNewContainer | bit | 1 | Yes/No required |
| NeedsPreservative | bit | 1 | Yes/No required |
| ChiefScientistID | int | 4 | Value populated by stored procedure |
| CollectionGroupID | int | 4 | Value populated by stored procedure |
| CollectionEventID | int | 4 | Value populated by stored procedure |
| CollectorID | int | 4 | Value populated by stored procedure |
| ContactID | int | 4 | Value populated by stored procedure |
| SampleID | int | 4 | Value populated by stored procedure |
| ParentSampleID | int | 4 | Value populated by stored procedure |
| ErrorMsg | varchar | 20 | Value populated by stored procedure |
»Next: Backups
Last updated: 28 April 2000 by Jennifer Paduan