SNP map weight

SNP map weight
the number of times a SNP map to the genome contig (range 1-10)

1 =	hit genome once, annotated on NT_ contigs
2 =	hit genome twice, annotated on NT_ contigs with warning
3 =	hit genome 3-9 times, not annotated
10 =	hit 10+ times on genome, not annotated

Working with the BULK INSERT Command

Reference: InformationIT.com

The BULK INSERT command is a new T-SQL statement found in SQL Server 7.0. BULK INSERT enables you to read a data file as if it were an OLE-DB recordset. This allows SQL Server to move the entire recordset into the table in one step, or in several steps, if you are using a predetermined batch size. Here is the syntax of the BULK INSERT command:

BULK INSERT [['database_name'.]['owner'].]{'table_name'
FROM data_file}[WITH
([ BATCHSIZE [= batch_size]]
[[,] CHECK_CONSTRAINTS]
[[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']]
[[,] DATAFILETYPE [= {'char' | 'native'| 'widechar' | 'widenative'}]]
[[,] FIELDTERMINATOR [= 'field_terminator']]
[[,] FIRSTROW [= first_row]]
[[,] FORMATFILE [= 'format_file_path']]
[[,] KEEPIDENTITY]
[[,] KEEPNULLS]
[[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]]
[[,] LASTROW [= last_row]]
[[,] MAXERRORS [= max_errors]]
[[,] ORDER ({column [ASC | DESC]}  [,...n])]
[[,] ROWS_PER_BATCH [= rows_per_batch]]
[[,] ROWTERMINATOR [= 'row_terminator']]
[[,] TABLOCK])]

• database_name'— The name of the database in which the table you want to bulk insert data into is located.
• data_file— The full path and filename you want to import.
• CHECK_CONSTRAINTS— Specifies that table constraints are checked during the bulk insert operation.
• CODEPAGE— Specifies the codepage found in the data file. This option is necessary only when the data is of a character datatype and uses extended characters.
• DATAFILETYPE— Specifies what format the data in the file is stored as.
• FIELDTERMINATOR— Specifies what character is used as a field terminator. The default is a tab or \t character.
• FIRSTROW— Specifies which row you want to start the bulk insert process with. The default is the first row in the data file (that is, FIRSTROW = 1).
• FORMATFILE— Specifies a full path and filename of a format file to be used in conjunction with the bulk insert. This works in a similar fashion to the format file created for the BCP command.
• KEEPIDENTITY— Specifies that IDENTITY values being bulk inserted will keep their value rather than having a new IDENTITY value assigned to them as part of the insert process.
• KEEPNULLS— Specifies that NULL values retain their NULL values when bulk inserted.
• KILOBYTES_PER_BATCH— Specifies the number of bytes to transfer at a time. There is no default value because the BULK INSERT command seeks to move the entire data set in one chunk.
• LASTROW— Specifies the last row of data to move. By default, this is the last row in the data file.
• ORDER— Allows you to specify the sort order within the data file. If the data file is sorted and has a different sort order than the data in your table, specifying this option can greatly enhance performance.
• ROWS_PER_BATCH— Specifies how many rows to move in each batch. You can use this option when BATCHSIZE is not specified.
• ROWTERMINATOR— Specifies the end of a row of data. The default is the newline or \n character.
• TABLOCK— Specifies that a table lock will be used for the duration of this bulk insert. This can have performance benefits for the bulk insert operation.

For additional information on using the BULK INSERT please refer to the Microsoft SQL Server Books Online, or SQL Server 7.0 Unleashed by Sams Publishing.

How to create a local copy of dbSNP

1.  dbSNP database organization brief overview:
dbSNP is a 'hub-and-spoke' design with dbSNP_main as the hub storing all the ‘lookup’ tables that are shared by each organism specific databases. Each spoke is an organism-specific database, named by organism_taxid(ex.: human_9606) that contains the latest data from submission, mapping, clustering, genotypes, haplotypes, validation etc.

2. Files needed to create a local copy of dbSNP:
To create a local copy of dbSNP, go to the following ftp directory:
ftp://ftp.ncbi.nih.gov/snp/database/
There are 4 subdirectories:
/organism_data: Data for each organism specific database.
/organism_schema:                  Schema DDL(Data Definition Language) for organism specific database.
/shared_data: Data in dbSNP_main that is shared by all organism database.
/shared_schema: Schema DDL for dbSNP_main.

3. Data File Format:
The entire data of dbSNP tables are dumped table-wise into files named as "tablename.bcp" under “shared_data” and “organism_data” subdirectories.  Fields of data within each file are tab delimited. (Note, this is an update to file on 11/22/99. The delimiter used to be '|'. ) ´

4. General Steps to create local dbSNP:
******************************************************************
First create dbSNP_main with schema files in /shared_schema and data in
/shared_data.  After dbSNP_main is created, create the organism
specific database using files in /organism_schema and /organism_data. 
For example, to create human_9606 database:

In ftp://ftp.ncbi.nih.gov/snp/database/shared_schema/:

Use dbSNP_main_table.sql.gz to create tables for dbSNP_main.
Use dbSNP_main_view.sql.gz to create views for dbSNP_main.
Use dbSNP_main_index_contraint.sql.gz to create indexes for dbSNP_main.

In ftp://ftp.ncbi.nih.gov/snp/database/shared_data/:
Use the <table>.bcp file to load into the tables.

Create database human_9606 with files in
ftp://ftp.ncbi.nih.gov/snp/database/organism_schema/human_9606,

human_9606_table.sql.gz
human_9606_view.sql.gz
human_9606_index_constraint.sql.gz 
human_9606_foreign_key.sql.gz      

Then load human_9606 table data with *.bcp files in
ftp://ftp.ncbi.nih.gov/snp/database/organism_data/human_9606.

Local dbSNP database should be re-created with each build, using the
latest schema file to ensure that all tables will be correctly loaded.

GUIDE
A sample Unix C shell script cmd.create_local_dbSNP has been provided
in ftp site:  ftp://ftp.ncbi.nih.gov/snp/database/loadscript.

cmd.create_local_dbSNP.txt shows how to use dbSNP_table.sql and *.bcp
to create a local sql database.  Users of other database platform can
use the same method with modifications of platform specific commands.

cmd.create_local_dbSNP first creates all tables without indexes. This
allows fast load of bcp data.  It then loads table data using a second
script cmd.bulkinsert.txt and compares lines in the data file with
table row counts as error checking.

Stepwise Procedure for Creating a Local Copy of dbSNP

http://www.ncbi.nlm.nih.gov/books/NBK21088/#ch5.ch5_s6

For fileformat description, see:

ftp://ftp.ncbi.nih.gov/snp/00readme.txt

Preparation for the project: SNP database(NCBI) structure

Reference:
http://www.ncbi.nlm.nih.gov/projects/SNP/snp_summary.cgi

dbSNP Component Availability Dates:

Component	Date available
dbSNP web query for build 135:	Oct 13, 2011
ftp data for build 135:	Nov 03, 2011
Entrez Indexing for build 135:	Oct 28, 2011
BLAST database for build 135:	Nov 14, 2011

-  The complete data for build 135 are available at ftp://ftp.ncbi.nih.gov/snp/ in multiple formats.
-  All formats and conventions are described in ftp://ftp.ncbi.nih.gov/snp/00readme.txt.


 DATABASE STRUCTURE
Since the inception of build 125, the design of dbSNP has been altered to a "hub
and spoke" model, where the dbSNP_main table acts as the hub of a wheel, storing
all of the central tables of the database, while each spoke of the wheel is an
organism-specific database that contains the latest data for a specific
organism.

Schema Documentation Source:
dbSNP uses standard SQL DDL(Data Definition Language) to create schema tables,
views for those tables, and indexes.  The FTP database directory
(ftp://ftp.ncbi.nih.gov/snp/database/) contains the schema, data, and SQL
statements to create the tables and indices for dbSNP:

The organism_schema (ftp://ftp.ncbi.nih.gov/snp/database/organism_schema/) sub-
directory contains links to the schema DDL for each organism specific database.



*** Creating a local copy of dbSNP ***

Instructions for creating a local copy of dbSNP are located at:
ftp://ftp.ncbi.nih.gov/snp/database/README.create_local_dbSNP.txt  OR
http://www.ncbi.nlm.nih.gov/books/bv.fcgi?rid=handbook.section.ch5.ch5-s6

*** Importing sections of dbSNP into a local spreadsheet ***

The ".bcp" files in the organism subdirectories "/organism_data" and
"/shared_data" may be loaded into most spreadsheet programs by setting the field
delimiter character to "TAB".