0

I have a table, Office, with a column that contains lists of ids representing many-to-many relationships:

TABLE Office {
   OfficeId int IDENTITY(1,1) NOT NULL,
   CubeIds NVARCHAR(MAX) NULL, --values like "8,6,7"; "10,12,14,16"
}

and a destination junction table:

TABLE OfficeToCube {
   CubeId INT NOT NULL,
   OfficeId INT NOT NULL,
   FOREIGN KEY (CubeId) REFERENCES (Cube.CubeId),
   FOREIGN KEY (OfficeId) REFERENCES (Office.OfficeId),
}

How can I iterate over both the rows and the comma-separated values in CubeIds, in order to insert them as individual records in OfficeToCube?

nivomi
  • 11
  • 2
  • @mustaccio The linked question is asking how to work around a column like this one while leaving it in place. The first answer says that resolving the structural issue is more ideal, but does not actually provide a method for resolving it. Instead, they answer the question as asked and provide a workaround. I would like to resolve this problem at a a structural level by turning it into a proper junction table. – nivomi Sep 30 '23 at 01:31
  • Not sure I understand what exactly you have a problem with: a CREATE TABLE statement? an INSERT statement? something else? – mustaccio Sep 30 '23 at 01:49
  • @mustaccio I've edited the question to add more detail. Please let me know if this fails to clarify the issue. – nivomi Sep 30 '23 at 02:05
  • @nivomi The linked Post from mustaccio answers your question. It provides a method for converting the comma delimited list of values into rows tied to their other related columns from the original table. If you follow this same methodology then all you have to do is add an INSERT statement to save the results to your OfficeToCube table. Granted, if you were on SQL Server 2016 or newer, this would be an easier problem to solve. – J.D. Sep 30 '23 at 03:22

0 Answers0