473,703 Members | 4,219 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Bulk Insert

pk
Sorry for the piece-by-piece nature of this post, I moved it from a
dormant group to this one and it was 3 separate posts in the other
group. Anyway...

I'm trying to bulk insert a text file of 10 columns into a table with
12. How can I specify which columns to insert to? I think format
files are what I'm supposed to use, but I can't figure them out. I've
also tried using a view, as was suggested on one of the many websites
I've searched, but I clearly did that incorrectly as well.

--------------------------------
Update:
I'm working with the view, and I've got a view that contains the exact
columns from the table I want. I ran my bulk insert command,
BULK INSERT Test..IV10401 FROM 'c:\bulkInsertF ile2.txt'
and it returned the error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'IV10401' with unique index
'AK2IV10401'.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.
The AK2IV10401 key is comprised of 3 columns and I'm sure that each of
my rows in the insert file is unique according to those three. What
should I be checking for?

-----------------------
Update 2:
I can only successfully insert 1 row. It seems to be treating each row

as an individual primary key when it should be treating them as
composite keys. I cannot alter the table, since it was created by
Great Plains Dynamics. Is there some sort of switch that I'm missing
in my bulk insert statement or can I suppress the errors?

Jul 23 '05 #1
6 12352
pk
ughh, bulk insert is going to be the end of me. i just need to insert
two seperate .txt files into two separate tables, but i can't do it. i
did finally get one to go through by not demanding that the index
AK2IV10401 is unique. i don't know what problems that will cause for
me in the future, but i would at least like to get to the future to see
SOMETHING happen. As for the second table, there is a Primary Key that
is blocking all my progress and I don't know how to get around this.
Here is the error I get.

Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
duplicate key in object 'IV10402'.
The statement has been terminated.

I REALLY don't think I'm violating anything, so why is it kicking and
screaming at me?

-pk

Jul 23 '05 #2
pk (ph**********@g mail.com) writes:
Sorry for the piece-by-piece nature of this post, I moved it from a
dormant group to this one and it was 3 separate posts in the other
group. Anyway...

I'm trying to bulk insert a text file of 10 columns into a table with
12. How can I specify which columns to insert to? I think format
files are what I'm supposed to use, but I can't figure them out. I've
also tried using a view, as was suggested on one of the many websites
I've searched, but I clearly did that incorrectly as well.
Format files are a bit tedious, but for 10 columns it's not that
bad. Here is an example:

8.0
10
1 SQLCHAR 0 0 "\t" 1 X ""
2 SQLCHAR 0 0 "\t" 2 X ""
...
10 SQLCHAR 0 0 "\r\n" 12 X ""

First row is the version of the file format. Next row lists the number
of fields in the bulk file. Next ten rows details the fields.

First column is the field number. Second column number is the data type.
This is always SQLCHAR for an ANSI file, and SQLNCHAR for a Unicode
file. Other data types applies only to binary data files.

Third column is prefix length. This is always 0 for a text file. Fourth
column is column length. Use this for fixed-length columns or leave 0.
Fifth column is the field terminator. In the example, I'm assuming
tab, save for the last row that is terminated by carriage return+line feed.

The sixth column is the column number for the table column in SQL Server.
This does not have to follow the numbers in the file. If the number is 0,
that file in the text file is not imported.

The seventh column is the column name, but this column is informational
only.

The eigth column specifies the collation. This is good if you need to
convert data between charsets when importing.

--------------------------------
Update:

I'm working with the view, and I've got a view that contains the exact
columns from the table I want. I ran my bulk insert command,

BULK INSERT Test..IV10401 FROM 'c:\bulkInsertF ile2.txt'

and it returned the error:

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'IV10401' with unique index
'AK2IV10401'.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.

The AK2IV10401 key is comprised of 3 columns and I'm sure that each of
my rows in the insert file is unique according to those three. What
should I be checking for?
Maybe the keys are already in the table?
-----------------------
Update 2:

I can only successfully insert 1 row. It seems to be treating each row

as an individual primary key when it should be treating them as
composite keys. I cannot alter the table, since it was created by
Great Plains Dynamics.
Without access to table definition, data file and the BCP command
it's hard to tell what is going on.

A common technique is to bulk load into a staging table, and then
clean up data there, before moving to the target table.
Is there some sort of switch that I'm missing
in my bulk insert statement or can I suppress the errors?


Well, you can use -b and -m to set the batch size, and increase the
number of errors permitted. See Books Online for further details.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
pk
I'll describe the table the best I can. Then I'm headed home for the
day in hopes that someone can explain my error. I appreciate your
response Erland but I feel I haven't given enough info. So here it
goes.

I've got an empty table IV10402. I didn't create the table, Great
Plains Dynamics did, but I need to import to it. There are several
indexes and primary keys defined on it that I can't, with good
confidence, alter. It has 11 columns and I have a txt file that
consists of 10, so I've created a format file which I'm fairly certain
is correct. It appears as follows.

----------------------------------

8.0
10
1 SQLCHAR 0 15 "\t" 1
PRCSHID SQL_Latin1_Gene ral_CP1_CI_AS
2 SQLCHAR 0 1 "\t" 2
EPITMTYP SQL_Latin1_Gene ral_CP1_CI_AS
3 SQLCHAR 0 31 "\t" 3
ITEMNMBR SQL_Latin1_Gene ral_CP1_CI_AS
4 SQLCHAR 0 9 "\t" 4
UOFM SQL_Latin1_Gene ral_CP1_CI_AS
5 SQLCHAR 0 41 "\t" 5
QTYFROM ""
6 SQLCHAR 0 41 "\t" 6
QTYTO ""
7 SQLCHAR 0 41 "\t" 7
PSITMVAL ""
8 SQLCHAR 0 41 "\t" 8
EQUOMQTY ""
9 SQLCHAR 0 41 "\t" 9
QTYBSUOM ""
10 SQLCHAR 0 12 "\n" 10
SEQNUMBR ""

-------------------------

So when I run my bulk insert command, which appears as follows,

BULK INSERT Test..IV10402 FROM 'c:\bulkInsertF ile.txt'
WITH (DATAFILETYPE=' char',
ROWTERMINATOR=' \n',
FORMATFILE='c:\ iv10402.fmt')

I get this error,

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
duplicate key in object 'IV10402'.
The statement has been terminated.

I then go to check what the Primary Key constraint PKIV10402 is and
here is the info that I can offer up.

It is not clustered. It is based off of 6 columns, PRCSHID, EPITMTYP,
ITEMNMBR, UOFM, QTYFROM, and QTYTO. The Create UNIQUE checkbox is
checked and greyed out, so I can't use my previous workaround of
checking the "Ignore Duplicate Key" box. Index Filegroup is PRIMARY.
Fill Factor is 90%.

One last thing is that the Table Identity Column for IV10402 is set to
DEX_ROW_ID, which happens to be the one column that I'm not inserting.
Is this a problem?

Again, this table is empty when I run this insert. I'm almost positive
that there aren't actually duplicate primary keys. Did Microsoft
really offer no way to find out which rows it feels are duplicates?
That seems very shortsighted in my opinion. Thanks for reading. I'll
see you all tomorrow.

-pk

Jul 23 '05 #4
pk (ph**********@g mail.com) writes:
ughh, bulk insert is going to be the end of me. i just need to insert
two seperate .txt files into two separate tables, but i can't do it. i
did finally get one to go through by not demanding that the index
AK2IV10401 is unique. i don't know what problems that will cause for
me in the future, but i would at least like to get to the future to see
SOMETHING happen. As for the second table, there is a Primary Key that
is blocking all my progress and I don't know how to get around this.
Here is the error I get.

Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
duplicate key in object 'IV10402'.
The statement has been terminated.

I REALLY don't think I'm violating anything, so why is it kicking and
screaming at me?


Because you are violating something.

Get data into a keyless staging table, and to a SELECT WHERE EXISTS
to find clashes with existing data, and "SELECT keycol, COUNT(*) FROM
tbl GROUP BY keyol HAVING COUNT(*) > 1" to find the dups in the file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
pk (ph**********@g mail.com) writes:
It is not clustered. It is based off of 6 columns, PRCSHID, EPITMTYP,
ITEMNMBR, UOFM, QTYFROM, and QTYTO. The Create UNIQUE checkbox is
checked and greyed out, so I can't use my previous workaround of
checking the "Ignore Duplicate Key" box. Index Filegroup is PRIMARY.
Fill Factor is 90%.

One last thing is that the Table Identity Column for IV10402 is set to
DEX_ROW_ID, which happens to be the one column that I'm not inserting.
Is this a problem?

Again, this table is empty when I run this insert. I'm almost positive
that there aren't actually duplicate primary keys. Did Microsoft
really offer no way to find out which rows it feels are duplicates?


Either there are duplicates in the file, or the format file is incorrect
somehow, so that data ends up in the wrong columns.

Create a copy of the table, but put no indexes or constraints on the
table. Bulk load data into that table. Check for duplicate with

SELECT col1, col2, ... COUNT(*)
FROM tbl
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1

Also, do something like "SELECT TOP 100 * FROM tbl" to see whether the
data makes any sense.

You can use the Object Broswer in Query Analyzer to create a script
for the table. Find the table, and scripting options is on the context
menu. Create the table in tempdb.

As for finding which rows that are problematic directly, BULK INSERT
does not seem to offer this option. BCP does, but I think that error
file covers only format errors, not insertion errors.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
pk
Erland,

You are a lifesaver. It took me a while to figure out what that SQL
statement you were telling me to use was supposed to do, but as soon as
I did, it found the 2 lines out of 15000 that managed to trip the
duplicate key error. I've since corrected it and am feeling much more
confident in my troubleshooting skills for the future. Thank you very
much.

-pk

Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
16504
by: php newbie | last post by:
Hello, I am trying to load a simple tab-delimited data file to SQL Server. I created a format file to go with it, since the data file differs from the destination table in number of columns. When I execute the query, I get an error saying that only sysadmin or bulkadmin roles are allowed to use the BULK INSERT statement. So, I proceeded with the Enterprise Manager to grant myself those roles. However, I could not find sysadmin or...
5
4897
by: me | last post by:
I'm also having problems getting the bulk insert to work. I don't know anything about it except what I've gleened from BOL but I'm not seeming to get anywhere...Hopefully there is some little (or big) problem with my code that someone can point out that may save me some time. TIA CBL
7
12125
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the servers using sp_addlinkedserver on both database servers. When I call the Commit API of oledb I get the following error: Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:
16
17011
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then IF FOUND UPDATE <TABLE> SET .... <Values entered here> ELSE INSERT INTO <TABLE> VALUES <Values...
3
33233
by: moonriver | last post by:
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those records one by one, but the performance is very poor: it takes more than 6 hours to finish the loading. So could I make use of the bulk-insert mechanism of SQL Server to reduce the loading time in C# codes? or other performance improvement solutions?
2
14651
by: Ted | last post by:
I have BULK INSERT T-SQL statements that work for all of my basic data tables except for one. Here is the problem statement (with obvious replacement of irrelevant path info): BULK INSERT igbconts_tmp FROM 'C:\\my_code_path\\IGBCONTS.txt' WITH (KEEPNULLS, FORMATFILE = 'C:\\my_data_path\\contacts.fmt');
3
3459
by: Tim Satterwhite | last post by:
Hi All, I think this is a thorny problem, and I'm hoping you can help. I've not found this exact issue described anywhere yet. I have a stored procedure that calls BULK INSERT on a set of text files. These files are FTP'd from a legacy system (a mainframe running MVS). Sometimes, the process steps on iteslf, whereby the bulk insert is attempted on a file whose FTP is still in progress; it's not fully written to disk on the SQL box...
0
3011
by: ozkhillscovington | last post by:
We have sp's in place that do BULK INSERTS from txt files into the tables. This works fine, however they have asked us to add a field that identifies accounting ctr. The only thing that identifies accounting ctr is the last three letters of the text file being used for the BULK INSERT. How would you suggest that I do this? Is there a way to add a default value to the .fmt or schema files for the text fields, or in the BULK INSERT...
0
2091
by: rshivaraman | last post by:
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) --------------------------------- This is the query used to populate bill_tbl. Actually this baddress.dat contain rowdelimiter of \r\n.
0
8761
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8674
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9262
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9018
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8970
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6595
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5923
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
2463
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.