locked
How to insert IDENTITY primary key to another column of the same table RRS feed

  • Question

  • User899592849 posted

    Hello,

    This might be silly but How can I insert the values (IDENTITY) of [Id] [int] IDENTITY(1,1) of tbl_Primary to another column of the same table named PrimaryID? It is like duplicate the ID column.  I was be able to insert the IDENTITY to the second table named tbl_Guest.  See code below. Thank you.

    using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["XYZ"].ConnectionString))
                {
                    con.Open();
    
                    using (SqlCommand cmd = new SqlCommand("" +
                        "INSERT INTO tbl_Primary (First_Name,Last_Name,Phone,PrimaryID) VALUES (@First_Name,@Last_Name,@Phone,(Select SCOPE_IDENTITY()));" +
                        "INSERT INTO tbl_Guest (GuestFirst_Name,GuestLast_Name,Guest_Phone,tbl_PrimaryId) VALUES (@GuestFirst_Name,@GuestLast_Name,@GuestPhone, (Select SCOPE_IDENTITY()))", con))
    
                    {
                        cmd.CommandType = CommandType.Text;
                        {
    
                            cmd.Parameters.AddWithValue("@First_Name", "test");
                            cmd.Parameters.AddWithValue("@Last_Name", "test");
                            cmd.Parameters.AddWithValue("@Phone", "test");
                            cmd.Parameters.AddWithValue("@GuestFirst_Name", "test");
                            cmd.Parameters.AddWithValue("@GuestLast_Name", "test");
                            cmd.Parameters.AddWithValue("@GuestPhone", "test");
    
                            cmd.ExecuteNonQuery();
    
                        }
                    } 
                    
                }
    Tuesday, March 12, 2019 3:05 AM

All replies

  • User465171450 posted

    You can't without doing an update. The identity value isn't available until after the insert completes. It's not even available at a trigger level. You could look at making the other column a computed column that replicates the identity, but really I can't think of any good situation where you would want to do this.

    Tuesday, March 12, 2019 4:22 AM