0

Is there a way to set IDENTITY_INSERT ON for table valued type? The way how it is done with tables - isn't working.

CREATE TYPE dbo.tvp_test AS TABLE
(
    id INT NOT NULL IDENTITY(1, 1),
    a  INT NULL
);
GO

DECLARE @test dbo.tvp_test;

SET IDENTITY_INSERT @test ON;

INSERT INTO @test VALUES (1, 1);

DROP TYPE dbo.tvp_test;

Error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@test'

3
  • IDENTITY_INSERT allows you to alter a table specifically, not a variable. If you need a type which has an IDENTITY and one that does not, you'll need 2 types. Commented Nov 16, 2020 at 9:09
  • Why on earth would you want that anyway? IMHO, using identity insert should only be used when copying data from one table to another. At least, I can't seem to think of any other valid use-case for it. Commented Nov 16, 2020 at 9:52
  • @ZoharPeled because I want to have this sequence generated automatically in 1 case and be predefined in another Commented Nov 16, 2020 at 9:59

2 Answers 2

2

Is there a way to set IDENTITY_INSERT ON for table valued type?

TL;DR: No.


SET IDENTITY_INSERT is a command to be used against a table object, not a variable. SET IDENTITY_INSERT (Transact-SQL):

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table. ##Syntax

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }  

Arguments

database_name
Is the name of the database in which the specified table resides.

schema_name
Is the name of the schema to which the table belongs.

table_name
Is the name of a table with an identity column.

Notice that this makes no reference to a variable at all; that's because it can't be used against one.

If you do need two versions of a Table Type, one that allows explicit values of its ID column, and the other that uses an IDENTITY, you will need to define 2 table types; one with an IDENTITY property and the other without:

CREATE TYPE dbo.tvp_test_i AS TABLE (id INT NOT NULL IDENTITY(1, 1),
                                     a INT NULL);

CREATE TYPE dbo.tvp_test_ni AS TABLE (id INT NOT NULL,
                                     a INT NULL);
GO

DECLARE @i dbo.tvp_test_i;
INSERT INTO @i (a)
VALUES(17),(21);

DECLARE @ni dbo.tvp_test_ni;
INSERT INTO @ni (id,a)
VALUES(3,95),(5,34);

SELECT *
FROM @i;

SELECT *
FROM @ni;
Sign up to request clarification or add additional context in comments.

Comments

0

You could accommodate both ids (auto-generated and/or manually specified) in the same table type with some added overhead:

CREATE TYPE dbo.tvp_test_xyz AS TABLE
(
    autoid INT NOT NULL IDENTITY(1, 1), --auto generated id
    manualid int null, --manually inserted id (filled in when needed)
    id as isnull(manualid, autoid) unique, --the final id, this is used in queries etc....
    a  INT NULL
);
GO


declare @t as dbo.tvp_test_xyz ;

--case, use autogenerated id
insert into @t(a)
select top (100) object_id
from sys.all_objects;

--id = autoid
select *
from @t;

select * from @t
where id between 10 and 20;

--case, manual id
delete from @t;

insert into @t(manualid, a)
select top (100) row_number() over(order by name desc), object_id
from sys.all_objects;

--id = manualid
select * from @t;

select * from @t
where id between 10 and 20;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.