Solved

BULK INSERT

Posted on 2007-04-03
3
216 Views
Last Modified: 2008-02-01
HI,

I have having a little trouble with this T-SQL procedure.

I need it to auto generate the ID field when I execute the command against my file.  But it keeps coming back with an error about Not for Replication.

Has anyone had experience of this when you have an Identity Column?

Thanks in advance

Andrew
0
Comment
Question by:REA_ANDREW
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18842492
>I have having a little trouble with this T-SQL procedure.
which one?

anyhow:
when loading (external) files to sql tables, I can only recommend 1 thing:
use staging tables to bulk load the data to, validate there, and the copy to the final table.
see also here:
http://www.sqlteam.com/item.asp?ItemID=3207


0
 
LVL 20

Author Comment

by:REA_ANDREW
ID: 18842508
I am using this

            BULK INSERT theusedcarsearch.dbo.tbl_Cars
               FROM 'D:\home\Default\theusedcarsearch.com\htdocs\Feeds\UsedCarSql.sql'
               WITH
                 (
                    FIELDTERMINATOR ='¦',
                    ROWTERMINATOR = '\r\n',
                    KEEPIDENTITY
                  )

completes 60,000 rows in 30 seconds, so for speed it is for me BUT, it complains about the ID column when I have the parameter KEEPIDENTITY

inline with

http://msdn2.microsoft.com/en-us/library/ms188365.aspx
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18842576
KEEPIDENTITY is when you want to insert the value for the identity field.
if your file does not contain such a column, you have 2 choices:
* the staging table as mentionned above
* create a view on the table that only returns the columns that the file contains, and use that view name in the BULK INSERT

note:
  if the table is part of replication, you CANNOT use BULK insert directly on the table and you have only option 1) -> BULK INSERT into staging table , and normal INSERT into your final table.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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