Conclusion Insert or Remove in SQL Server

Question

I have a table SalesTransactionand Invoice:

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int
)

and

Create Table Invoice
(
     InvoiceId int  Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

What I want is a table Invoiceidfrom Invoicefor a Invoiceidtable tag SalesTransaction.

First, Invoiceidof SalesTransactionis NULL, and after adding data to the invoice table, it should return to the InvoiceidtableSalesTransaction

Answer 1

First solution (only with new foreign key)

For the following scheme

Create Table Invoice
(
     InvoiceId int Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int Foreign key references Invoice(InvoiceId)
)

Here are your INSERT / UPDATE queries

DECLARE @SalesTransactionId as int
DECLARE @InvoiceId as int

INSERT INTO [dbo].[SalesTransaction]
           ([CustomerId]
           ,[ProductId]
           ,[Price]
           ,[Quantity]
           ,[Total]
           ,[InvoiceId])
     VALUES
           (1
           ,1
           ,1
           ,1
           ,1
           ,NULL)

SET @SalesTransactionId = SCOPE_IDENTITY()
SET @InvoiceId = 1

INSERT INTO [dbo].[Invoice]
           ([InvoiceId]
           ,[InvoiceAmount]
           ,[BalanceAmount]
           ,[AmountPaid])
     VALUES
           (@InvoiceId
           ,1
           ,1
           ,1)

UPDATE [dbo].[SalesTransaction]
   SET [InvoiceId] = @InvoiceId
 WHERE SalesTransactionId = @SalesTransactionId
GO

I also suggest you set the InvoiceIdtable column Invoiceas an identifier.

Second solution (with new foreign key and identifier for invoice pk)

For the following scheme

Create Table Invoice
(
     InvoiceId int Identity(1,1) Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int Foreign key references Invoice(InvoiceId)
)

Here are your INSERT / UPDATE queries

DECLARE @SalesTransactionId as int
DECLARE @InvoiceId as int

INSERT INTO [dbo].[SalesTransaction]
           ([CustomerId]
           ,[ProductId]
           ,[Price]
           ,[Quantity]
           ,[Total]
           ,[InvoiceId])
     VALUES
           (1
           ,1
           ,1
           ,1
           ,1
           ,NULL)

SET @SalesTransactionId = SCOPE_IDENTITY()

INSERT INTO [dbo].[Invoice]
           ([InvoiceAmount]
           ,[BalanceAmount]
           ,[AmountPaid])
     VALUES
           (1
           ,1
           ,1)

SET @InvoiceId = SCOPE_IDENTITY()

UPDATE [dbo].[SalesTransaction]
   SET [InvoiceId] = @InvoiceId
 WHERE SalesTransactionId = @SalesTransactionId
GO