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 8 (of 8 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!

Add RowID Column to Bulk Insert

Cookies on SQLServerCentral