Solved

Doing a bulk insert in SQL Server 2012

Posted on 2013-01-07
7
686 Views
Last Modified: 2013-05-30
I am trying to do a bulk insert from a csv file in SQL Sercer 2012:

BULK
INSERT Alias
FROM 'E:\filepath.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

The problem is, the csv is missing a column that the table has. The table has Alias_ID that is supposed to auto increment by one every time a row is inserted. The CSV does not have this column so I get errors when I try to do this bulk insert. How can I handle a bulk insert where the columns do not match? I did try to edit the csv file so that there were blank values for the first column which I created called AliasID and just leaving a blank and a comma to signify no data but I guess that would be invalid since an ID column should not be null. Thanks in advance.
0
Comment
Question by:DB-aha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38752676
in the case the file and the table don't match you need to use a format file:
http://msdn.microsoft.com/en-us/library/ms178129.aspx
0
 
LVL 1

Author Comment

by:DB-aha
ID: 38752866
angelIII, in looking at the link you provided, it seems I have to use something called a bcp utility to first create the formatted file, then include the WTH FORMAT FILE reference in my code. The problem is, I have on idea what bcp is, how to use it. Could you break down the work process into something that is step by step to what I have to do/ Books online hasn't always been as informative as it could be.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38753596
bcp is a command line tool to simplify the format file creation, but you can create the format file by hand also (see Sample Format Files in the page).
you basically create the format file in a folder on the sql server box, and refer to it WITH FORMAT FILE indeed.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:DB-aha
ID: 38759800
Ok, dealing with the original problem, I modified the input file to have a column heading of RecordID and left all of the column blank to allow for the field names in both the input file and the destination table to be the same. The table's recordID column is the identity column and is set up to increment for every row inserted. When I do the bulk import now, all of the information imports and the record ID populates itself with the increment for the identity key but I get the error when doing the bulk insert:

The query completed with errors:

Msg 4864, Level 16, State 1, Line 8
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Record_ID).


However the rows all insert. What can I do to eliminate this error being generated since the input file is a csv file. and the record ID is set as INT to increment by 1 staring with 1?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38763904
for identity fields, it should be skipped, again via the format file.
if the csv file contains the id value to apply, you need to specify that you want to apply the IDENTITY_INSERT on.
0
 
LVL 1

Author Comment

by:DB-aha
ID: 38788892
Please walk me through in simple terms using a list of instructions of how I can use the format tool.
0
 
LVL 1

Author Closing Comment

by:DB-aha
ID: 39207689
I haven't had time to try this, but this is a viable solution from my understanding of what this is.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 57
access to sql migration 5 26
SQL syntax question 6 45
Need a mirrored QA test site 2 17
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question