SQL INSERT with OUTPUT generates many rows instead of only 1

[Rand_H]

Hi,

I think I have a very easy to reproduce error related with SQL server Database.

The issue appears when you insert a new register in a table and you use the OUTPUT clause. This is very useful when you have a auxiliary table when you want to insert new value and get the auto generated ID of the inserted row.

So you need a new table for testing:

Spoiler

CREATE TABLE [dbo].AUX_Test(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Project_Alias] [varchar](100) NULL
CONSTRAINT [PK_Aux_Test] 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]
GO

Then you insert a row using a INSERT INTO "Open SQL statement" sentence in TestStand. We will expect to have a new row in the DB table and we can then query the ID generated by the SQL server of this new row using a "data operation" sentence.

From the TS point of view everything seems OK but if look at the DB table it has inserted two rows instead of one, and have returned the ID of the second one.

I attach also the test sequence in case anybody want to test in their enviroment.

I'm using TS2017 and SQL server 2008. SQL connection provider is "SQLOLEDB" but has the same results using the SQL native client "SQLNCLI11".