Solved

BULK INSERT

Posted on 2007-04-03
3
213 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 142

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 142

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Slabs 9 37
Can someone plz fix this..getting an error 3 18
Serach for record in Microsoft SQL Management Studio 8 20
Sql Query 4 16
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now