inserting incremental numbers into table

  • Hi group,

    Table ABC - non of the columns are Identity

    Structure of ABC: RecID Int(4) - Not Identity

                             Name varcahr(100)

                             Address varchar(100)

    Now I have to insert Name and Address from table XYZ and increment values for RECID for every record inserted. Can you please let know how can I do that? Thanks in advance.

  • Change the column type to IDENTITY.

    _____________
    Code for TallyGenerator

  • I want to change column type to IDENTITY through stored procedure. Is it possible?

  • CREATE TABLE dbo.tmp_ABC

     (

     RecID int NOT NULL IDENTITY (1, 1),

     Name varchar(100) NOT NULL,

     Address varchar(100) NOT NULL 

    SET IDENTITY_INSERT dbo.tmp_ABC ON

    INTO dbo.Tmp_ABC

    (RecID , Name , Address )

    SELECT RecID , Name , Address 

    FROM dbo.ABC TABLOCKX

    SET IDENTITY_INSERT dbo.Tmp_ABC OFF

    DROP TABLE dbo.ABC

    EXECUTE sp_rename N'dbo.Tmp_ABC', N'ABC', 'OBJECT'

    GO

    If there are FK constraints you need to drop it first and recreate in new table.

     

    _____________
    Code for TallyGenerator

  • You can't change a column to add or remove the Identity property.  The table-editing function in Enterprise Manager or Management Studio may make it look easy, but in both cases they are building a new table and copying all the data.  And recreating all foreign keys, indexes, and everything else.  And possibly breaking existing code that refers to the table.  This is not a trivial operation that you want to put in a stored procedure to be run repeatedly.

    If you want to leave the RecID column alone (non-identity) but still want to insert sequentially-numbered records, you can do that by first inserting the records into a temp table with a properly-seeded identity column and then inserting the records with good RecID values into the target table.

    DECLARE

    @cmd varchar(8000)

    SELECT @cmd = replace('SELECT IDENTITY(int, <seed>, 1) as RecID, Name, Address INTO #newrecs FROM xyz', '<seed>', max(RecID)+1)

    FROM abc

    EXEC (@cmd)

    INSERT INTO abc (RecID, Name, Address)

    SELECT RecID, Name, Address FROM #newrecs

    DROP TABLE #newrecs

    This script assumes that no new records will be inserted into abc by other users.  If that is a possibility, you could use max(RecID)+10 or +100 to leave a gap, or put it in a transaction and use "FROM abc WITH(TABLOCK, HOLDLOCK)" to prevent conflicts.

Viewing 5 posts - 1 through 4 (of 4 total)

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