SQL Server和C#主/明细插入 [英] SQL Server and C# Master / Detail Insert

问题描述

我正在创建将从我的C#应用​​程序中调用的存储过程，并将在SQL Server中输入主/详细信息.在页眉表上，我将TransactionId作为身份"列，以便每次插入时都获得唯一的ID.当我为明细表调用存储过程时，我想使用标题中的PK ID并将其插入到明细表的FK中.确保我获得在Header表中刚刚创建的ID的最佳方法是什么?

I am creating stored procedures that will be called from my C# application and will enter master/detail information into SQL Server. On the Header table I have TransactionId as an Identity column so that I get a unique ID every time I insert. When I call my stored procedure for the detail table, I would like to use the PK ID from the Header and insert it into the FK of the detail. What is the best way to ensure that I get back the ID that was just created in the Header table?

CREATE TABLE [dbo].[Header835]
(
   [TRANSACTIONID] [int] IDENTITY(1, 1) NOT NULL
   , [FILENAME] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
   , [TRADINGPARTNERNAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
   , [ISACRTL] [numeric](18, 0) NOT NULL
   , [STCRTL] [numeric](18, 0) NOT NULL
   , CONSTRAINT [PK_Header835] PRIMARY KEY CLUSTERED
   (
      [TRANSACTIONID] ASC
   ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

推荐答案

您将想要使用 SCOPE_IDENTITY()

You'll wan to use SCOPE_IDENTITY()

DECLARE @headerTransactionId INT;

INSERT INTO dbo.Header835(Filename, TradingPartnerName, IsACrtl, StCrtl)
SELECT 'myFile.txt', 'Joe', 103, 123;

SELECT @headerTransactionId = SCOPE_IDENTITY();    

SQL小提琴 示例

这篇关于SQL Server和C#主/明细插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！