Solved

BULK INSERT

Posted on 2007-04-03
3
220 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
[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
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

627 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