1

I want to do something like

DECLARE @idTable TABLE
(
    hierakiId INT,
    katId INT
);

DECLARE @id int
SET @id = (SELECT MIN([ID]) FROM [Hieraki] WHERE Navn = 'Sagsskabeloner')

INSERT INTO HierakiMedlem(Navn, HierakiID) 
OUTPUT INSERTED.ID, s.ID INTO @idTable
SELECT s.Navn, @id, s.ID FROM SagSkabelonKategori s

UPDATE s SET s.HierakiMedlem = @idTable.hierakiId
FROM SagSkabelon s INNER JOIN @idTable
ON s.SagSkabelonKategoriID = @idTable.katId

resulting in a map in @idTable, mapping the old to the new identity of each category, so that i can change references as needed. Obviously this results in an error (3rd line) as the SELECT results in more columns than used by the INSERT INTO.

Any suggestions on the cleanest way to do this?

I'm on SQL Server 2005.

/edit

now w. complete source code.

We are switching from a semi-flat, non-nested category sorting, to a hierachy based one. All the categories are to be copied as root level nodes in the new hierachy, and the former members of each category must have a new field set referencing the newly created root node.

what i want to do; 1. Copy all categories to the hierachy table, setting their parent (HierakiID) to the same value.

  1. update a column in all references to the categories so they now (also) reference the hierachy nodes.

  2. delete references to categories

  3. delete categories

the tricky part for me is to get a map between the category id and the hierachy id.

/edit

hhravn
  • 1,701
  • 1
  • 10
  • 22
  • You can use `merge` instead of `insert`. Look here http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id and here http://stackoverflow.com/questions/6174355/how-to-copy-tables-avoiding-cursors-in-sql/6174409#6174409 – Mikael Eriksson Jun 06 '11 at 13:14
  • sadly not a possibility. Forgot to mention that I'm on SQL Server 2005. – hhravn Jun 06 '11 at 13:53

1 Answers1

0

In an INSERT statement OUTPUT can only project columns from the INSERTED table. And your SELECT must match the INSERT. Assuming HierakiMedlem.ID is an generated identity value, then try something like:

INSERT INTO HierakiMedlem(Navn, HierakiID) 
OUTPUT INSERTED.ID, INSERTED.HierakiID 
   INTO @idTable (ID, HierakiID)
SELECT s.Navn, s.ID 
FROM SagSkabelonKategori s

Your subsequent update uses column names like @idTable.katId which is not possible for me to guess what it means to be. So is likely my answer won't compile directly, but if you want a correct answer you should, always, include the exact definition of your tables (including table variables).

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569