Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: load multiple txt files to sql server Date: Sun, 25 May 2014 11:16:47 +0200 Organization: Erland Sommarskog Lines: 30 Message-ID: References: <2fe392b1-5878-466c-842f-b4a40ab9f917@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx05.eternal-september.org; posting-host="0c412830dbc354c08130c47cfccdbc83"; logging-data="19433"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+AzsCbDV9Too2HBOT79es7" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:5vfSGKeNJB7mNseY+ciOoav7KF8= Xref: csiph.com comp.databases.ms-sqlserver:1758 (rja.carnegie@gmail.com) writes: > As for the nation value that isn't in the data file itself, > I think you can set a constant in a format file that > controls a BCP data import process, No, you can't. > but in my experience the format file is difficult to construct Yes, as long as you have not learnt the format. > But this doesn't exist if I am reading this correctly > > (SQL Server 2014) which says "A data file can contain > fewer fields than the number of columns in the table > only if the skipped columns are nullable and/or have > default value." i.e. you can't write a default value > into the format file to use instead. > Correct. But you can change the default value for each column before you load. Which still it is a bit of a kludge. I would rather solve it with client-side language. Just because you can do it in T-SQL does not mean that you have to. -- Erland Sommarskog, Stockholm, esquel@sommarskog.se