How to insert an identity value into another table

Question

I have two tables:

create table Clients
(
     id_client int not null identity(1,1) Primary Key,
     name_client varchar(10) not null,
     phone_client int not null
)

create table Sales
(
     id_sale int not null identity(1,1) Primary Key,
     date_sale date not null, 
     total_sale float not null
     id_clients int not null identity(1,1) Foreign Key references Clients
)

So, let's insert into Clients ('Ralph', 00000000), the id_client is going to be 1 (obviously). The question is: How could I insert that 1 into Sales?

Answer 1

This works for me like a charm, because as far as I understand, if I migrate user or customer data etc from an old copy of the database, - the identity column and the UId(user id) or CId(customer id) used to give each row it's own unique identity - will become unsynced and therefor I use the second column(TId/UId etc) that contains a copy of the identity column's value to relate my data purely through app logic flow.

I can offset the actual SQL Identity Column(IdColumn) when a migration takes place via inserting and deleting dummy data to the count of the largest number found in that TId/CId etc column with the old data to increment the new database table(s) identity column(s) away from having duplicates on new inserts by using the dummy inserts to push the identity column up to old data values and therefor new customers or users etc will continue to source unique values from the identity column upon new inserts after being filled with the old data which will still contain the old identity values in the second column so that the other data(transactions etc) in other tables will still be in sync with which customer/user is related to said data, be it a transaction or whatever. But not have duplicates as the dummy data will have pushed up the IdColumn value to match the old data.

So if I have say 920 user records and the largest UId is 950 because 30 got deleted. Then I will dummy insert and delete 31 rows into the new table before adding the old 920 records to ensure UId's will have no duplicates. It is very around the bush, but it works for my limited understanding of SQL XD

What this also allows me to do is delete a migrated user/customer/transaction at a later time using the original UId/CId/TId(copy of identity) and not have to worry that it will not be the correct item being deleted if I did not have the copy and were to be aiming at the actual identity column which would be out of sync if the data is migrated data(inserted into database from old database). Having a copy = happy days. I could use (SET IDENTITY_INSERT [dbo].[UserTable] ON) but I will probably screw that up eventually, so this way is FOR ME - fail safe.

Essentially making the data instance agnostic to an extent.

I use OUTPUT inserted.IdColumn to then save any pictures related using that in the file name and am able to call them specifically for the picture viewer and that data is also migration safe.

To do this, a copy, specifically at insert time, is working great.

declare @userId INT = SCOPE_IDENTITY() to store the new identity value-UPDATE dbo.UserTable to select where to update all in this same transaction -SET UId = @userId to set it to my second column -WHERE IdColumn = @userId; to aim at the correct row

USE [DatabaseName]
GO
/****** Object:  StoredProcedure [dbo].[spInsertNewUser]    Script Date: 
2022/02/04 12:09:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertNewUser]
@Name varchar(50),
@PhoneNumber varchar(20),
@IDNumber varchar(50),
@Address varchar(200),
@Note varchar(400),
@UserPassword varchar(MAX),
@HideAccount int,
@UserType varchar(50),
@UserString varchar(MAX)


AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO dbo.UserTable
    ([Name],
    [PhoneNumber],
    [IDNumber],
    [Address],
    [Note],
    [UserPassword],
    [HideAccount],
    [UserType],
    [IsDeleted],
    [UserString])
    OUTPUT inserted.IdColumn
VALUES
    (@Name,
    @PhoneNumber,
    @IDNumber,
    @Address,
    @Note,
    @UserPassword,
    @HideAccount,
    @UserType,
    @UserString);

    declare @userId INT = SCOPE_IDENTITY()
    
    UPDATE  dbo.UserTable
    SET     UId = @userId
    WHERE   IdColumn = @userId;

END

Here is the create for the table for testing

CREATE TABLE [dbo].[UserTable](
[IdColumn] [int] IDENTITY(1,1) NOT NULL,
[UId] [int] NULL,
[Name] [varchar](50) NULL,
[PhoneNumber] [varchar](20) NULL,
[IDNumber] [varchar](50) NULL,
[Address] [varchar](200) NULL,
[Note] [varchar](400) NULL,
[UserPassword] [varchar](max) NULL,
[HideAccount] [int] NULL,
[UserType] [varchar](50) NULL,
[IsDeleted] [int] NULL,
[UserString] [varchar](max) NULL,
 CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED 
(
[IdColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Answer 2

FIrst of all - you cannot have two columns defined as identity in any table - you will get an error

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Sales'. Only one identity column per table is allowed.

So you will not be able to actually create that Sales table.

The id_clients column in the Sales table references an identity column - but in itself, it should not be defined as identity - it gets whatever value your client has.

create table Sales
(
     id_sale int not null identity(1,1) Primary Key,
     date_sale date not null, 
     total_sale float not null
     id_clients int not null foreign key references clients(id_client)
)

-- insert a new client - this will create an "id_client" for that entry
insert into dbo.Clients(name_client, phone_client)
values('John Doe', '+44 44 444 4444')

-- get that newly created "id_client" from the INSERT operation
declare @clientID INT = SCOPE_IDENTITY()

-- insert the new "id_client" into your sales table along with other values
insert into dbo.Sales(......, id_clients)
values( ......., @clientID)