March 19, 2015 at 10:06 am
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
cGroupVARCHAR(7),
cValueCHAR(1))
GO
ALTER TABLE tTable
ADD CONSTRAINT FK_Parent
FOREIGN KEY (nParent_Id)
REFERENCES tTable (nRow_Id)
GO
INSERTtTable (cGroup, cValue)
SELECT'One', 'A'
UNION ALL SELECT 'One', 'B'
UNION ALL SELECT 'One', 'C'
GO
UPDATEtTable
SETnParent_Id = nRow_Id
WHEREcValue = 'A'
GO
UPDATEtTable
SETnParent_Id = tmp1.nRow_Id
FROMtTablet1
CROSS JOIN(SELECT nRow_Id
FROMtTable
WHEREcValue = 'A') AS tmp1
WHEREcValue = 'B'
GO
UPDATEtTable
SETnParent_Id = tmp1.nRow_Id
FROMtTablet1
CROSS JOIN(SELECT nRow_Id
FROMtTable
WHEREcValue = 'B') AS tmp1
WHEREcValue = 'C'
GO
SELECT*
FROMtTable
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
Maybe I need more coffee, I can't seem to get it...
March 19, 2015 at 12:06 pm
Well, this is one (bad?) way to do it:
INSERT INTO tTable (nParent_id, cGroup, cValue)
SELECTx.nParent_Id + y.maxid , 'Two', cValue
FROMtTable x
CROSS APPLY (
SELECTMAX(NRow_id) AS maxid
FROMtTable
) y
WHEREx.cGroup = 'One'
ORDER BY x.nRow_Id
March 19, 2015 at 12:10 pm
Hmm not quite: let's say there are x groups, and the parents' ids aren't always sequential.
March 19, 2015 at 12:27 pm
You should add those requirements into your question then 🙂
What about this one? More complex but i couldn't find anything better either
;WITH CTE AS (
SELECTROW_NUMBER() OVER(ORDER BY nRow_id) + maxid AS neworder
,nParent_id
,x.nRow_Id
,cValue
FROMtTable x
CROSS APPLY (
SELECTMAX(NRow_id) AS maxid
FROMtTable
) y
WHEREx.cGroup = 'One'
)
INSERT INTO tTable (nParent_id, cGroup, cValue)
SELECT t2.neworder, 'Two', t.cValue
FROMCTE t
INNER JOIN CTE t2
ONt2.nrow_id = t.nParent_id
ORDER BY t.nRow_id
This code won't work if you have gaps in your IDENTITY, but IDENTITIES shouldn't be used here anyway 🙂
March 19, 2015 at 5:58 pm
How about something simpler like this?
INSERT INTO #Table
SELECTnParent_id + SCOPE_IDENTITY(), cGroup, cValue
FROMtTable;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 20, 2015 at 6:50 am
Although that does work nicely in the scenario I presented, that won't work in for my real-life case: there are other inserts into identity tables earlier in the procedure.
I should probably also have mentioned that Parent + Group + Value is unique.
I solved it. Cursors! er, make that "Curses!" 😛
Since the code will be called on average once a month, and involves roughly a dozen rows, I'm not especially bothered by a cursor solution.
Thanks for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply