Insert with Output clause keeping track of old and new values

Question

Insert with Output clause keeping track of old and new values

westy

SSC Enthusiast

Points: 122
More actions
March 24, 2010 at 4:54 pm

#231123

hi
i have the following TSQL
what i am trying to do is keep track of the new DashboardComponentId and existing DashboardComponentId, this is because another table needs updating afterwards which has an existing DashboardComponentId, what i aim to do is insert new values here using the new dashboardcomponentid based on the original dashboardcomponentid so i have like for like in this table but with the new dashboardcomponentid instead of the exisiting one.
Declare @InsertedRows Table (NewRowId int, OldRowId int)
Insert USysDashboardComponents (DashboardId, DashboardControl, Layout)
Output inserted.DashboardComponentId, USysDashboardComponents.DashboardComponentId
Into @InsertedRows
Select 99, udc.DashboardControl, udc.Layout
From
USysDashboardComponents udc
Where
udc.DashboardId = 1
Select * From @InsertedRows
am i doing something wrong or is there a better way of doing this
cheers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

Yes you are doing something wrong. You are failing to punctuate. This makes your questions and comments all run together and is discouraging to people who are trying to help you. Nothing is l33t about failing to write like a grown up.

You are also failing to show us what output you are actually getting, and what output you expect to get. Most of us volunteers don't even pretend to be mindreaders.

Now that I'm off my soapbox, could you please help us out by giving us a little more information. It's much easier for us to work with specific data and examples than with lengthy word-descriptions.

😉

There is a great article[/url] about how to properly set up your question. Try it and you'll find people jumping quickly to offer you TESTED solutions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

westy SSC Enthusiast Points: 122 More actions · Answer 2

after looking at another topic on this forum i came up with the following solution which i should have done in the beginning 🙂

Declare @InsertedRows Table (NewRowId int, OldRowId int)

MERGE INTO USysDashboardComponents

USING (SELECT DashboardId, DashboardControl, Layout, DashboardComponentId FROM USysDashboardComponents WHERE DashboardId = 1) AS X

ON X.DashboardId = 0

WHEN NOT MATCHED THEN INSERT (DashboardId, DashboardControl, Layout) VALUES (47, X.DashboardControl, X.Layout)

OUTPUT inserted.DashboardComponentId as NewRowId, X.DashboardComponentId AS OldRowId

Into @InsertedRows (NewRowId, OldRowId);

Select * From @InsertedRows

In reply to Dixie, i did not want to go into too much detail just a brief outline initially

Desired output is the following

USysDashboardComponents has the following records

1, 'Name 1', 'Layout 1', 1

1, 'Name 2', 'Layout 2', 2

so what i wanted was to select the dashboard components with a dashboard id of 1, these two records would then get reinserted into the table under a new dashboard id e.g. 47

so we would then have the following records

1, 'Name 1', 'Layout 1', 1

1, 'Name 2', 'Layout 2', 2

47, 'Name 1', 'Layout 1', 3

47, 'Name 2', 'Layout 2', 4

i wanted to keep a track of old dashboardcomponentid and new

so i now return the following values

oldid newid

1 3

2 4

I need this as another table i have holds component ids only but i want to make a copy of the existing component details and just insert a copy of the existing record with the new component id

So taking the two records above, i am looking at putting these into a cursor and looping through, creating my copy of the component records so existing record of component id 1, would now have a duplicate but with a component id of 3.

Hope this is a little bit clearer.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

MUCH clearer, to me at least. I'm glad you found a workable solution. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills