| Author |
 Topic  |
|
|
Hommer
Aged Yak Warrior
693 Posts |
 Posted - 11/05/2012 : 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!
|
Edited by - Hommer on 11/20/2012 11:36:43
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 11/05/2012 : 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. |
Edited by - Hommer on 11/20/2012 11:37:59 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47081 Posts |
Posted - 11/05/2012 : 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/
|
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 11/06/2012 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47081 Posts |
Posted - 11/06/2012 : 12:14:29
|
ok..cool
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
how to retrieve PK value from last insert?
