Post reply

Incremental value for field using INSERT

  • April 19, 2005 at 2:51 am

    #66944

    I am populating a table from another table in the following way :-

     

    INSERT INTO Transactions1 (

              Field1,

              Field2,

              Field3,

              Field4

              )

     

    SELECT

              Field1,

              Field2,

              Field3,

              Field4

     

    FROM Transactions2

     

     

    The problem is field3 is an incremental value, eg for simplicity’s sake say it goes up by 1 for each record. Can someone please show me how to do it?

     

    Thanks in anticipation.

     

  • April 19, 2005 at 3:12 am

    #553364

    I think you are refering to identity filed

    so you start with

    SET IDENTITY_INSERT [dbo].[Transactions1 ] ON

    YOUR CODE

    SET IDENTITY_INSERT [dbo].[Transactions1 ] OFF




    My Blog: http://dineshasanka.spaces.live.com/

  • April 19, 2005 at 3:17 am

    #553365

    Thanks for your reply Dinesh.

    I don't want to use identity fields. I want to do it programmatically.

    I was thinking more of starting of with a value (a variable) of say 1000. So for every record created in Transactions1 field3 will have value of 1001, 1002, 1003 etc (ie the variable being incremented by 1).

     

     

  • April 19, 2005 at 3:47 am

    #553374

    oh! sorry for the mistake

    is field1 your primary key of the table?

     




    My Blog: http://dineshasanka.spaces.live.com/

  • April 19, 2005 at 3:48 am

    #553375

    Yes.

  • April 19, 2005 at 10:00 am

    #553478

    Took your advice in the end Dinesh and went with IDENTITY.

    There doesn't seem a way to do it as I wanted to. Thanks anyway.

  • April 19, 2005 at 1:56 pm

    #553579

    What will this be used for? Do you need to store such data in your tables or is this for presentation purposes? Keep in mind that you sooner or later almost always will have gaps in the IDENTITY sequence.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 20, 2005 at 10:08 am

    #553836

    Assuming that you really do need to do this, what about using a cursor?

    CREATE PROCEDURE FinancialAidAwardReformat

    AS SET NOCOUNT ON

    DECLARE @StudentID  char(9)

    DECLARE @Lastname varchar(50)

    DECLARE @Firstname varchar(50)

    DECLARE @FiscalYear char(9)

    DECLARE @Field3 int

    SELECT @Field3 = 0  --initialize field

    DECLARE abc CURSOR FOR SELECT StudentID, Lastname, Firstname, F.FiscalYear FROM dbo.tblFinancialAidAwards

    OPEN abc

    FETCH NEXT FROM abc INTO @StudentID, @Lastname, @Firstname, @FiscalYear 

    WHILE @@FETCH_STATUS = 0 

    BEGIN

    INSERT INTO tblWhatever

    (StudentID, Lastname, Firstname, FiscalYear, Field3)

    VALUES(@StudentID, @Lastname, @Firstname, @FiscalYear, @Field3 + 1)    

    FETCH NEXT FROM abc INTO @StudentID, @Lastname, @Firstname, @FiscalYear 

    END

    CLOSE abc

    DEALLOCATE abc

    GO



    Dana
    Connecticut, USA
    Dana

  • April 20, 2005 at 10:38 am

    #553841

    In answer to your queries as to why I need to do it :-

    Basically, this is what the system is doing.

    Transactions are stored in a table called Transactions. This table includes

    fields called Transaction Code and Trans Reference.

    Usually, the information is all copied to a second table called

    Transactions_ForProcessing.

    However, if a transaction code 400 is found, then an additional record has to be created, in Transactions_Forprocessing,with the same information but with a transaction code of 451. However, the Trans Reference

    has to also be different (it has to be unique, no duplicates allowed).

     

  • April 20, 2005 at 10:40 am

    #553843

    Thanks for your reply DanaH1976 . We've gone with the identity function.

Viewing 10 posts - 1 through 9 (of 9 total)

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