Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add RowID Column to Bulk Insert Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 9:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, 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!
Post #677596
Posted Tuesday, March 17, 2009 9:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #677604
Posted Tuesday, March 17, 2009 9:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #677610
Posted Tuesday, March 17, 2009 10:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #677638
Posted Tuesday, March 17, 2009 10:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, 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!
Post #677657
Posted Tuesday, March 17, 2009 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,115, Visits: 14,984
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?
Post #677662
Posted Tuesday, March 17, 2009 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #677668
Posted Tuesday, March 17, 2009 11:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:19 AM
Points: 83, 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!
Post #677729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse