mssql-cmd - SQL Server interactive and batch query command-line tool »

Download mssql-cmd Free Trial »

Load SQL Server data from flat file

parameters:

delimeter: delimiter char ("|",",","#","TAB","WHITESPACE")

quote: quote char (quote=' => single quotes, quote=" => double quotes)

header: y|n, data file contains field name at first line

schema: schema name, use this parameter to load specific schema's data, if not set, use logon user's default schema

table: table name

datafile: data file name

loadtype: append|replace|update|upsert
	append: appends the new rows to the table.
	replace: all rows in the table are deleted and the new data is loaded.
	update: update the rows by the new data.
	upsert upsert the rows by the new data.

updatekey: key feild to update/upsert with, usually use primary key.

dateformat: date format (e.g. "yyyy-mm-dd hh24:mi:ss")

timestampformat: timestamp format (e.g. 'yyyy-mm-dd-hh24.mi.ss.ff6')

nullif: specifies that the column should be loaded as NULL 
("BLANKS" for zero length strings being loaded into numeric columns)

badfile: write the content that can not load to this badfile

errors:  maxinum error count

quit = y|n (quit mssql-cmd after run in batch mode)


example

In interactive mode:

mssql-cmd> load table=media_messages 
      > datafile=f:\media_messages.txt 
      > delimiter=, 
      > quote=" 
      > header=y;



In batch mode:
	 
mssql-cmd dsn="pan:withdata@192.168.0.102:1433/test" task=load table=media_messages datafile=f:\media_messages.txt delimiter=, quote=" header=y quit=y