Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to retrieve PK value from last insert?

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-05 : 17:22:51
Hi, everyone,
What is the sure way to retrieve the PK my insert just created? The PK is a unique identifier.

I have a situation where I have to do something like reverse the cascade delete. The programmer deleted some rows from 3 tables. They want to put them back. He did have the data inserted into a single staging table. So, child1, child2 and parent tables are all in one table

So when I tried insert like next:
Insert into parent_table1 (fld1, fld2,…)
Select from fld1, fld2,…from stagingTable
GO
Insert into child1_table1 (fld11, fld12,…)
Select from fld11, fld12,…from stagingTable
GO
Insert into child2_table1 (fld21, fld22,…)
Select from fld21, fld22,…from stagingTable
GO
I got msg 2627
--Msg 2627, Level 14, State 1, Line 1
--Violation of PRIMARY KEY constraint
I understand the PK dosen’t allow duplicate value. My thought is that since it is unique identifiyer, I should be able to insert everyting else but the PK, and get the newly generated PK value from parent to use as FK for my child table insert.
Is there any flaw, and how do I retrieve the PK?

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-05 : 17:32:35
I think I see my problem.

The error is not because the parent table already has that PK value, but it is because my staging table has duplicate rows for the parent.

However it gave me ntext data type error when I tried to use select distinct...

That is a different beast.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-05 : 21:05:22
quote:
Originally posted by Hommer

I think I see my problem.

The error is not because the parent table alsready has that PK value, but it is because my staging table has duplicate rows for the parent.

However it gave me ntext data type error when I tried to use select distinct...

That is a different beast.


why are you using ntext? ntext is deprecated so try to use nvarchar(max) instead

if you cant change it in table CAST it to nvarchar(max) in SELECT and then apply distinct ...

...
SELECT DISTINCT....

...
CAST(column AS nvarchar(max))
..
FROM ...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-11-06 : 08:59:09
Thanks! I will remember that trick next time. I found out those ntext fields contains all null, so I simply skipped them.

The data type ntext was determined by the vendor.

quote:
Originally posted by visakh16

quote:
Originally posted by Hommer

I think I see my problem.

The error is not because the parent table alsready has that PK value, but it is because my staging table has duplicate rows for the parent.

However it gave me ntext data type error when I tried to use select distinct...

That is a different beast.


why are you using ntext? ntext is deprecated so try to use nvarchar(max) instead

if you cant change it in table CAST it to nvarchar(max) in SELECT and then apply distinct ...

...
SELECT DISTINCT....

...
CAST(column AS nvarchar(max))
..
FROM ...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:14:29
ok..cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -