Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Inserting a unique value in a column

    hi,
    In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.

    CRAETE TABLE Symp_Quote
    (
    QuoteRevision_ID INT PK,
    DocumentSeq TINYINT PK,
    DocumentDisplayName VARCHAR (255)
    )
    1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.

    INSERT INTO Symp_Quote
    (
    QuoteRevision_ID,
    DocumentSeq,
    DocumentDisplayName
    )
    SELECT
    S.QuoteRevision_ID,
    IDENTITY (INT,1,1) AS DocumentSeq,
    T.Name,
    FROM Symp S
    INNER JOIN Trilogy T
    ON
    SQR.tril_gid = FQR.tril_gid

    kindly correct me whr I am wrong.........


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't really get the question but this will probably help you
    Code:
    CREATE TABLE table999 (
        field1 int IDENTITY PRIMARY KEY
      , field2 char(1)
      ) 
    
    INSERT INTO table999(field2) VALUES ('a')
    INSERT INTO table999(field2) VALUES ('b')
    INSERT INTO table999(field2) VALUES ('c')
    INSERT INTO table999(field2) VALUES ('d')
    
    DELETE FROM table999 WHERE field1 = 3
    
    SELECT field1
         , field2
    FROM   table999
    
    SET IDENTITY_INSERT table999 ON
    
    INSERT INTO table999(field1, field2) VALUES (3, 'z')
    
    SET IDENTITY_INSERT table999 OFF
    
    SELECT field1
         , field2
    FROM   table999
    
    DROP TABLE table999
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    ur rgt georgy......... u din gt my issue.......... All i want is to insert some value in the 2nd column (DocumentSeq) so that the 1st 2 columns (QuoteRevision_ID, DocumentSeq) together becomes primary key.


    And for the same, thought of what could be better than just inserting the IDENTITY value in the column.........


    Thanks,
    Rahul Jha

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by DBA_Rahul
    ur rgt georgy......... u din gt my issue.......... All i want is to insert some value in the 2nd column (DocumentSeq) so that the 1st 2 columns (QuoteRevision_ID, DocumentSeq) together becomes primary key.


    And for the same, thought of what could be better than just inserting the IDENTITY value in the column.........


    Thanks,
    Rahul Jha
    Do you want something like this
    Code:
    SELECT 
    S.QuoteRevision_ID as QuoteRevision_ID,
    IDENTITY (INT,1,1) AS DocumentSeq,
    T.Name as DocumentDisplayName
    INTO Symp_Quote 
    FROM Symp S
    INNER JOIN Trilogy T
    ON
    S.tril_gid = T.tril_gid
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Thanks for the responce Rudra............ But I can't use INTO clause as the Table Symp_Quote already exists........



    Thanks,
    Rahul Jha

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Actually its not that, i can't use INTO clause. But to do that I have to migrate the data 1st into a temp table and then migrate it from temp to Symp_Quote . which I don't want.........

    Doing this will add an overhead of an extra insert.

    Thanks,
    Rahul Jha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •