Get Primary Key Value After Insert not knowing the Column Name of the Primary key

Question

I'm trying to figure out if there is a way to return the primary key value, during an insert, without knowing the Primary Keys Column Name.

I would like to use Output because not all of the Primary Keys in the Database are Integers, but I do not think it is possible to do a subquery for an Output.

 CREATE TABLE TestTable ( PkId INT Identity(1,1) NOT NULL, Value1 VARCHAR(50) NULL, Value2 VARCHAR(50) NULL, CONSTRAINT PkId PRIMARY KEY (PkId) ) INSERT INTO TestTable(Value1, Value2) OUTPUT INSERTED. --Primary Key to find here Values('Test', 'Test2')

I was thinking about using something like this as a subquery, but like I stated before I don't think it is possible to use an output with a subquery

SELECT column_name as PRIMARYKEYCOLUMN FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND KU.table_name='TestTable' ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

Any help would be greatly appreciated!

PkId is NOT NULL so why you're trying to insert NULL to it? — Ilyes
– Ilyes, Commented Sep 17, 2019 at 9:51
Going to be an Auto Increment sorry I will edit the post to state that — Eric
– Eric, Commented Sep 17, 2019 at 9:52
Okey, so since it's IDENTITY() what's the problem with INSERT INTO TestTable(Value1, Value2) OUTPUT INSERTED.PkId Values('Test', 'Test2')? — Ilyes
– Ilyes, Commented Sep 17, 2019 at 9:54
It seems strange that you know the names of all 'Value' columns but not the name of the 'PK' column. Can't think of a practical reason why or an actual scenario where that would be the only unknown column name. — Peter B
– Peter B, Commented Sep 17, 2019 at 9:57
I will be using this script inside of a c# application and I will be using this for multiple tables so I will not know the Column Name of the Primary Key. — Eric
– Eric, Commented Sep 17, 2019 at 9:57

Greg Low · Accepted Answer · 2019-09-17 10:14:31Z

You could use your query to find the primary key column name but you'd have to execute dynamic SQL to include that column name in the output clause. I've given you an example here but I've also modified it to use standard system views instead of the old information_schema views.

USE tempdb; GO CREATE TABLE dbo.TestTable ( PkId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_TestTable PRIMARY KEY, Value1 varchar(50) NULL, Value2 varchar(50) NULL ); GO DECLARE @SQL nvarchar(max) = N' INSERT INTO TestTable(Value1, Value2) OUTPUT INSERTED.' + QUOTENAME(( SELECT c.[name] FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON i.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.objects AS o ON i.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE i.is_primary_key <> 0 AND o.[name] = N'TestTable' AND s.[name] = N'dbo' )) + N' VALUES(''Test'', ''Test2''), (''Test3'', ''Test4'')'; EXEC (@SQL); GO

You can run it in tempdb to see how it works. You'll see it outputs the primary key value each time. Where you will have a problem is if there are multiple columns in your primary key. It can also be done but it's much more complicated code. I'm guessing you'll only have one column anyway so this should be fine.

Hope that helps.

Collectives™ on Stack Overflow

Get Primary Key Value After Insert not knowing the Column Name of the Primary key

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related