CODEWITHSUNDEEP
sql-serverazureazure-sql-databaseidentity-columnsql-insert
Matthew Moisen
Matthew Moisen

Reputation: 18299

How to Retrieve the most recent Identity value and use it in an INSERT statement in SQLServer/Azure?

[EDIT]

In response to Mitch Wheat's advice, inserts now work with (SCOPE_IDENTITY(), 'Semi-Fail'), (@@IDENTITY, 'Semi-Fail'); however, both produce NULL values on select:

create clustered index test2_inx ON test2 (test1_id);

The following INSERTs both produce NULL

INSERT INTO test1 VALUES ('Semi-failure');
INSERT INTO test2 VALUES (SCOPE_IDENTITY(), 'Semi-failure');
INSERT INTO test1 VALUES ('Semi-failure');
INSERT INTO test2 VALUES (@@IDENTITY, 'Semi-failure');
SELECT * FROM test2;

The use of IDENT_CURRENT does work, however:

INSERT INTO test1 VALUES ('Success');
INSERT INTO test2 VALUES (IDENT_CURRENT('test1'), 'Success');

... but why can't I get it to work with the other two?

Thank you.

As an aside: coming from Oracle, the notion that a table can be created as anything other than a heap table without being specified as such, is, well, insane. Is that a Cloud thing? Does anyone know the purpose of that?

[/EDIT]

DDL:

CREATE TABLE test1 (
 id int identity(1,1)
,value varchar(20)
,CONSTRAINT test1_id_pk PRIMARY KEY (id)
);
CREATE TABLE test2 (
 id int
,value varchar(20)
,CONSTRAINT test2_id_fk FOREIGN KEY (id) REFERENCES test1 (id)
)

Insert into test1 statement:

INSERT INTO test1 VALUES ('failure');

All of the following fail with the same error:

INSERT INTO test2 VALUES (SCOPE_IDENTITY(), 'fail');
INSERT INTO test2 VALUES (select SCOPE_IDENTITY(), 'fail');
INSERT INTO test2 VALUES ( (select SCOPE_IDENTITY()) , 'fail');
INSERT INTO test2 VALUES (@@IDENTITY, 'fail');
INSERT INTO test2 VALUES (select @@IDENTITY, 'fail');
INSERT INTO test2 VALUES ( (select @@IDENTITY), 'fail');
INSERT INTO test2 VALUES (IDENT_CURRENT('test1'), 'fail');
INSERT INTO test2 VALUES (select IDENT_CURRENT('test1'), 'fail');
INSERT INTO test2 VALUES ( (select IDENT_CURRENT('test1'), 'fail');

For each of the above INSERT statements, I receive one of the following errors:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
Incorrect syntax near the keyword 'select'.

In addition, the following select statements return null, after a INSERT into test1:

SELECT SCOPE_IDENTITY();
SELECT @@IDENTITY;

Whereas the following returns the correct value:

SELECT IDENT_CURRENT('test1');

Upvotes: 1

Views: 1383

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300718

You must define a clustered index on your tables (as described in the error message you posted).

Because Microsoft Windows Azure SQL Database does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

Ref.

Do that, and then SELECT SCOPE_IDENTITY() should work.

Upvotes: 1

Related Questions