Post reply

Adding Identity column data by updating with reseed value

  • June 11, 2012 at 1:13 pm

    #275660

    I have a question with Identity property

    Is there anyway where we can insert the data directly onto second column by ignoring first column.

    I have a csv file, which had IDENTITY COLUMN on first column and the next columns followed by data.

    This Identity propery has been set to (1,1).

    I want to insert this data into my database, but while inserting I want data of identity property to show from 1000, instead of 1.

    So, for this I tried using bulk insert, but got issues of column not matching

    BULK INSERT dbo.tablename

    FROM 'C:\..\.....\..\tablename.csv'

    WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\r', FIRSTROW=1)

    So, the second step I took is created a temp table and worked through naming conventions, by selecting column separetly which WORKED.

    My question is through bulk insert, can we do this process, where it avoids the first column and loads the data.

    Before loading data, I willl also implement

    DBCC CHECKIDENT ('Tablename', reseed, 1000).

  • June 11, 2012 at 9:28 pm

    #1499575

    DBA_SQL (6/11/2012)

    I have a question with Identity property

    Is there anyway where we can insert the data directly onto second column by ignoring first column.

    I have a csv file, which had IDENTITY COLUMN on first column and the next columns followed by data.

    This Identity propery has been set to (1,1).

    I want to insert this data into my database, but while inserting I want data of identity property to show from 1000, instead of 1.

    So, for this I tried using bulk insert, but got issues of column not matching

    BULK INSERT dbo.tablename

    FROM 'C:\..\vpasnur\..\tablename.csv'

    WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\r', FIRSTROW=1)

    So, the second step I took is created a temp table and worked through naming conventions, by selecting column separetly which WORKED.

    My question is through bulk insert, can we do this process, where it avoids the first column and loads the data.

    Before loading data, I willl also implement

    DBCC CHECKIDENT ('Tablename', reseed, 1000).

    Yes, you can, but you'll need to abstract things with a VIEW. So, say your staging table is created as:

    CREATE TABLE dbo.StagingTable

    (

    StagingTableID INT NOT NULL

    IDENTITY(1000, 1),

    Column1 VARCHAR(100) NULL,

    Column2 VARCHAR(100) NULL

    );

    You'll create a view defined as:

    CREATE VIEW dbo.StagingTableLoader

    AS

    SELECT Column1,

    Column2

    FROM dbo.StagingTable;

    Your bulk insert then becomes:

    BULK INSERT dbo.StagingTableLoader

    FROM 'C:\..\vpasnur\..\tablename.csv'

    WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\r', FIRSTROW=1)

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 12, 2012 at 6:43 am

    #1499752

    sounds good. Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

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