CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
SamJolly
SamJolly

Reputation: 6477

How to transfer data from one table to another 2 tables

I am using sql-server 2008.

I have 3 tables:

Item
  - Id
  - Name

ItemLink
  - Id
  - FkParentItemId
  - FkChildItemId

Item2
  - Id
  - FkItemId
  - Name

Relationships are:

ItemLink >- Item -< item2
         >-

I now realise I have made a mistake and need to transfer data from "Item2" to "Item", but create the hierarchy via inserting an ItemLink record. I do not wish to start a debate on the pros and cons of this approach, but just to add that there are common tables off "Item" that I need for "Item2"

So I need a heads up on the insert ddl required to do this please. I believe it would be something along the lines of:

Insert into Item(Name) select(Item2.Name) from Item2
Insert into ItemLink(FkParentItemId,FkChildItemId) select (Item2.FkItemId, New ItemId ??)

Bit rusty on this, hence the need for help.

Many thanks in advance.

Upvotes: 2

Views: 573

Answers (1)

Michael
Michael

Reputation: 1028

Ok so I am making some assumptions here.

I assume values in table Item2 are going to stay.

 insert into item (name)
 select Name from item2

This middle part is unclear to me. Somehow you will need to update the Fk to Item table.

 update item2
 set FkItemId = id
 from item2 inner join item 
 on item.name = item2.name

Populate ItemLink

 insert into ItemLink ( PkParent, PkChild)
 select item.id, item2.id
 from item2 inner join item 
 on item.id = item2.FkItemId

Upvotes: 1

Related Questions