Add RowID Column to Bulk Insert

Question

Add RowID Column to Bulk Insert

bkmooney

SSCrazy

Points: 2041
More actions
March 17, 2009 at 9:24 am

#128647

Hi! Sorry if this is a really basic question, but any help is definitely appreciated!
I'm loading a bunch of csv files into a table, and want to create a package using dts wizard & SSBIDS to run this a few times a week as files come in.
The files I'm importing are missing a RowID, which I would like to create during the upload process and be able to use as a primary key. ...but as I don't have a source row for it, I'm not really sure how to map nothing to something in the destination table.
Any ideas?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Does the RowID needs to be arbitrary? If not, then you can add an IDENTITY column to your destination table and leave the complexity for SQL Server to handle.

--Ramesh

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

If you don't use an IDENTITY column you can import the date into a temp table and use the following statement to add an incremental id:

IF (OBJECT_ID('tempdb..#test') IS NOT NULL)

DROP TABLE #test

CREATE TABLE #test (id INT, txt VARCHAR(100))

-- Some test data

INSERT INTO #test

SELECT TOP(100) NULL, name

FROM master.sys.columns

-- Create an incremental id

DECLARE @id INT

SET @id = 100

UPDATE #test SET @id = id = @id + 1

-- Result

SELECT * FROM #test

Now you can INSERT the data into the destination table.

Greets

Flo

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Florian Reischl (3/17/2009)
If you don't use an IDENTITY column you can import the date into a temp table and use the following statement to add an incremental id:

This should work, but it does not give the sequence ids according to the order of the input rows.

--Ramesh

bkmooney SSCrazy Points: 2041 More actions · Answer 4

Maybe I'm misunderstanding your suggestion - but I just tried to follow it, and was unsuccessful.

I named my RowID column [IDENTITY] (SQL added the brackets automatically, which I guessed meant I was on the right track...)

I set it to my primary key, so it is not allowed to contain NULL values.

Then when I reran the bulk load, I got the same error message, but with RowID replaced with IDENTITY:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'IDENTITY', table 'PWS_BKM.dbo.tblTradeHistory'; column does not allow nulls. INSERT fails.".

Am I not doing this right?

Thanks!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Don't NAME it "identity" - MAKE it an identity column. If you're using the UI - look in the properties of the column (identity specification section).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

You have to explicitly specify that you want the value of IDENTITY column to be generated by SQL Server. This can be set by un-checking the "Keep Identity" property of OLE DB Destination component in Data Flow Task.

--Ramesh

bkmooney SSCrazy Points: 2041 More actions · Answer 7

Great - I had to uncheck the box labeled "Enable Identity Insert" in the Edit Mappings part of the OLEDB Destination part of the wizard.

Thanks!