0

I have 3 tables which I want to combine into 2 new ones. Current model:

Table X

Id Description A Entry 1X B Entry 2X C Entry 3X

Table Y

Id Description 1 1Y 2 2Y 3 3Y

And now there's a intermediate table linking those 2 together in several combinations:

Table Z

IdX IdY Number1 Number2 A 1 1000 2000 A 2 300 400 A 3 790 7900 B 1 99 999 C 1 5000 500 C 3 250 2500

What I want to do is insert this data into 2 tables. One will be an intermediate again, the other containing all combinations of IdX and IdY (table 'code'). The intermediate will contain 2 foreign keys which are also combined PK's. One to employee and one to table 'code'). Besides these 2 FK's this table will contain the numbers of table Z. Example of both new tables:

Table code

Id Description 1 Entry 1X - 1Y 2 Entry 1X - 2Y 3 Entry 1X - 3Y

Table intermediate

IdEmployee IdCode Number1 Number2 Petersen 1 1000 2000 Petersen 3 790 7900 Benjamin 1 1000 2000 Benjamin 2 300 400
Benjamin 3 790 7900

So I have searched for this specific question but couldn't find exactly the same problem. Closest I found was: How can I INSERT data into two tables simultaneously in SQL Server?

But this is about 1 old record that is inserted into 2 new tables with 1 record in each new table. What I am doing is inserting a new record into table 'code' for each combination of X and Y found in the old intermediate table. Then I want to insert new records into the new intermediate table linking all new records in 'code' to all existing records in table Employee.

What I got to so far:

insert into code (Id, Column1, Column2, Description)
OUTPUT inserted.Id INTO intermediate (codeId, EmployeeId, Z.Number1, Z.Number2)
select id,0,1,CONCAT(x.Description,' - ',y.Descritpion)
from dba.Z
left outer join dba.X as x on x.Id = IdX
left outer join dba.Y as y on y.Id = IdY

But this won't work cause I haven't got a set of employees. Can anyone help me with a solution? Or do I really need to use a cursor?

Note: I have changed my source to anonymous data.

Community
  • 1
  • 1
Jeffrey
  • 105
  • 1
  • 1
  • 8

1 Answers1

0

Do that in steps. First create your table code with a reference to the existing tables: code(id, description, idx, idy).

Then insert records:

insert into code (idx, idy, description)
select x.id, y.id, x.description + ' - ' + y.description
from x
cross join y;

then create the table intermediate and fill it:

insert into intermediate (idcode, number1, number2, idemployee)
select code.id, z.number1, z.number2, employees.id
from code
join z on z.idx = code.idx and z.idy = code.idy
cross join employees;

Then you can remove idx and idy from table code and drop your old tables.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73