SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to retrieve PK value from last insert?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

766 Posts

Posted - 11/05/2012 :  17:22:51  Show Profile  Reply with Quote
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!


Edited by - Hommer on 11/20/2012 11:36:43

Hommer
Aged Yak Warrior

766 Posts

Posted - 11/05/2012 :  17:32:35  Show Profile  Reply with Quote
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.

Edited by - Hommer on 11/20/2012 11:37:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2012 :  21:05:22  Show Profile  Reply with Quote
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

766 Posts

Posted - 11/06/2012 :  08:59:09  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/06/2012 :  12:14:29  Show Profile  Reply with Quote
ok..cool

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000