T-SQL. Still need an identity value from SuperType table although there are no values to insert

Question

Hello.

Rightly or wrongly I’m dealing with a generalised SuperType table which I have the sub type data for but not the SuperType data. However to insert the SubType data I obviously need the Identity ID from the SuperType table first.

I was wondering if anyone else has come across this and how they got around it. Unfortunately there is no Type Column in the SuperType table as the SuperType can potentially have two types in the SubType tables. Otherwise I would just populate the type column. Alternatively I thought I could insert a blank Value (not null) to the Super table but this just seems very very wrong.

Am I stuck with this problem or is there a cunning way around it I’m just not seeing?

Many thanks

Answer 1

Why don't you add a DateCreated column to your SuperType Table?

CREATE TABLE dbo.SuperType
(
SuperTypeId INT IDENTITY(1,1),
DateCreated DATETIME
)
Go

Answer 2

Is this Microsoft SQL Server? You don't need to insert any values or have a redundant column. Try this:

CREATE TABLE dbo.SuperType (SuperTypeId INT NOT NULL IDENTITY PRIMARY KEY);

INSERT INTO dbo.SuperType DEFAULT VALUES;

SELECT SCOPE_IDENTITY();