2023年7月13日发(作者：)

从sqlserver中数据写⼊mysql_[SQLServer]SQLServer数据库中。。。问题描述SQL Server数据库中，有时候当我们使⽤INSERT INTO语句写⼊数据后，需要返回写⼊数据对应的⾃增ID或者GUID，以便根据此记录进⾏后续的操作。那么SQL语句如何实现返回记录值的操作呢？⽰例数据表结构：SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Customer]([Id] [int] IDENTITY(1,1) NOT NULL,[CustomerGuid] [uniqueidentifier] NULL,[LoginName] [nvarchar](50) NULL,[DisplayName] [nvarchar](50) NULL,CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_CustomerGuid] DEFAULT (newid()) FOR [CustomerGuid]GOALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_LoginName] DEFAULT ('') FOR [LoginName]GOALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_DisplayName] DEFAULT ('') FOR [DisplayName]GO⽅案⼀使⽤语句INSERT INTO TABLE (COLUMNS) OUTPUT _NAME VALUES (VALUES)语法结构可在写⼊数据后返回对应的字段值，⽐如这⾥我们需要返回数据表Customer的⾃增字段Id，则可以如下：INSERT INTO Customer (LoginName,DisplayName) OUTPUT VALUES ('rector','Rector');此语句不仅可以返回⾃增字段Id的值，还可以返回⾮⾃增的标识字段的值，⽐如数据表Customer的CustomerGuid字段，同样适⽤，如下：INSERT INTO Customer (LoginName,DisplayName) OUTPUT erGuid VALUES ('rector','Rector');⽅案⼆使⽤SCOPE_IDENTITY()函数返回标识字段的新值，如下：INSERT INTO Customer (LoginName,DisplayName) OUTPUT erGuid VALUES ('rector','Rector');SELECT SCOPE_IDENTITY();注：此⽅式只能返回标识字段的新写⼊数据对应的值。⽅案三这是在使⽤SCOPE_IDENTITY()函数的优化⽅案，你可以使⽤整条新数据记录，如下：INSERT INTO Customer (LoginName,DisplayName) OUTPUT erGuid VALUES ('rector','Rector');SELECT * FROM Customer WHERE Id = SCOPE_IDENTITY();