sql-server


Skip Column in OPENROWSET (BULK)


Trying to bulk insert lots of rows into a table.
My SQL statement:
INSERT INTO [NCAATreasureHunt-dev].dbo.CatalinaCodes(Code)
SELECT (Code)
FROM OPENROWSET(BULK 'C:\Users\Administrator\Desktop\NCAATreasureHunt\10RDM.TXT',
FORMATFILE='C:\Users\Administrator\Desktop\NCAATreasureHunt\formatfile.xml') as t1;
10RDM.TXT:
DJKF61TGN7
Q9TVM16Z6Z
X44T4169FN
JQ2PT1ZXZK
C7NW71QPNG
SFJRR1FWKZ
TYZJW1ZPFY
9MR3M1J3N5
QJ6R217JTK
TVJVW19TYT
formatfile.xml
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="C1" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="C1" NAME="Code" xsi:type="SQLNVARCHAR" />
</ROW>
</BCPFORMAT>
This is the error I'm getting:
Cannot insert the value NULL into column 'Claimed', column does not allow nulls. INSERT fails.
I'm trying to skip the Claimed column. What am I doing wrong in my format file?
See if this answer helps.
With an XML format file, you cannot skip a column when you are
importing directly into a table by using a bcp command or a BULK
INSERT statement. However, you can import into all but the last column
of a table. If you have to skip any but the last column, you must
create a view of the target table that contains only the columns
contained in the data file. Then, you can bulk import data from that
file into the view.
To use an XML format file to skip a table column by using
OPENROWSET(BULK...), you have to provide explicit list of columns in
the select list and also in the target table, as follows:
INSERT ... SELECT FROM OPENROWSET(BULK...)

Related Links

SQL INSTEAD OF Condition not evaluating
Setup psftp for use in SSIS
SQL Server convert ' dd/MM/YYYY H:mm:ss p.m..' to datetime
load into Fact Table
Sql query including a unicode character returns wrong result
How do I validate SQL tables data using a schema
C# SQL connection string best practice
SQL Server combine rows into single column
Sum monthly amount and output for last day of month
SSRS referring to Database Project in the solution
Error when trying to execute stored procedure
Get actual database size in SQL Server 2012 in GB?
Simple results list from SQL Server stored procedure using Codefluent
SQL Report Error - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name
How to get distinct column in SQL Server?
Powershell returns negative exit code while script results are correct

Categories

HOME
erlang
tinyos
npm
freepascal
warnings
extract
command
couchdb-2.0
user
websphere-liberty
eclipse-cdt
criteria
wicket
tee
uiview
equalizer
mod-pagespeed
netezza
chocolatey
primes
eclipselink
android-fragmentactivity
richfaces
tarantool
multiplayer
restfb
scalajs-react
non-deterministic
switching
katharsis
mef2
xilinx-ise
ggmap
superscript
crt
fractals
shapes
modelandview
rhomobile
qtablewidget
nlb
inkscape
code-behind
active-model-serializers
ical-dotnet
pdftk
resuming-training
bids
filepath
gauss
react-native-fbsdk
adler32
shinyjs
sqldatareader
atlassian-crowd
manifest.mf
nofollow
pagekit
contour
selecteditem
freefem++
ninject.web.mvc
application-loader
twython
shift-jis
subversion-edge
stackframe
custom-url
word-2013
ticket-system
rhel5
errorprovider
node-inspector
firebug-lite
log4cplus
funq
humanizer
aquafold
proxies
level
layered
nscolor
trailing-slash
qsqltablemodel
shim
shared-objects
hardware-acceleration
revert
unreachable-code
delegatecommand
grooveshark
hmacsha1
standardized
usergroups
self-reference

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App