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.
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 tableSo when I tried insert like next:Insert into parent_table1 (fld1, fld2,…)Select from fld1, fld2,…from stagingTableGOInsert into child1_table1 (fld11, fld12,…)Select from fld11, fld12,…from stagingTableGOInsert into child2_table1 (fld21, fld22,…)Select from fld21, fld22,…from stagingTableGOI got msg 2627 --Msg 2627, Level 14, State 1, Line 1--Violation of PRIMARY KEY constraintI 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. |
|
|
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) insteadif 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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) insteadif 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 MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-06 : 12:14:29
|
ok..cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|