Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add RowID Column to Bulk Insert


Add RowID Column to Bulk Insert

Author
Message
bkmooney
bkmooney
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 251
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!
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 2643
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
Florian Reischl
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2227 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 2643
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
bkmooney
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 251
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)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8525 Visits: 18280
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
Ramesh Saive
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 2643
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
bkmooney
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 251
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search