Foreign key assignment with set based insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Foreign key assignment with set based insert

Author

Topic

LaurieCox

158 Posts

Posted - 2012-04-26 : 09:45:59

Edit: This problem is most likely not solvable. There is another related problem that might be. See my other topic: Foreign key assign (part 2 ... More Complicated) (See post in that topic by visakh16 explaining why this is ONE not solvable).

I have these two tables:


CREATE TABLE Medications(
	MedicationId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	StartDate datetime,
	EndDate datetime,
) 

CREATE TABLE Instructions(
	InstructionId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	MedicationId int foreign key references Medications(MedicationId) not null,
	StartDate datetime,
	EndDate datetime
)

I need to do an insert into both tables. When inserting into the Instructions table I need to use the correct foreign key assignment.

So I have this data:


CREATE TABLE InputData(
	[GroupID] [int] NULL,
	[OrderNumber] [int] NOT NULL,
	[EventType] [varchar](1) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
) 
select 178248, 3, 'C', '2008-10-02 00:00:00.000', '2008-12-23 14:00:47.000' union all
select 178248, 5, 'C', '2009-03-16 00:00:00.000', '2009-06-14 00:00:00.000' union all
select 178248, 1, 'N', '2008-05-05 00:00:00.000', '2008-07-10 00:00:00.000' union all
select 5834, 3, 'R', '2010-01-19 16:57:06.000', '2010-01-26 00:00:00.000' union all
select 5834, 1, 'N', '2010-01-19 00:00:00.000', '2010-01-19 16:28:02.000' union all
select 5834, 5, 'C', '2010-03-02 00:00:00.000', '2010-03-09 00:00:00.000' union all
select 178248, 4, 'R', '2008-12-23 14:00:47.000', '2009-03-16 00:00:00.000' union all
select 178248, 6, 'R', '2009-06-17 14:19:08.000', '2009-09-15 00:00:00.000' union all
select 178248, 2, 'C', '2008-07-10 00:00:00.000', '2008-10-02 00:00:00.000' union all
select 5834, 2, 'R', '2010-01-19 16:28:02.000', '2010-01-19 16:57:06.000' union all
select 5834, 4, 'C', '2010-01-26 00:00:00.000', '2010-02-25 00:00:00.000'

The Medications table has one record for each N(ew) order. So I use this insert statement:


insert into Medications (StartDate,EndDate)
select StartDate
     , EndDate
  from InputData
 where EventType = 'N'

Now I need to insert rows into the Instructions table. I want a row in this table for all N(ew) and C(hanged) records. The foreign key in this table should point back to the record in the Medications table based on the GroupId.

I have solved the problem by adding GroupId to the Medicaitons table:


CREATE TABLE Medications(
	MedicationId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	StartDate datetime,
	EndDate datetime,
	GroupId int
)

And then write my insert statement like so:


insert into Instructions (MedicationId,StartDate,EndDate)
select m.MedicationId
     , i.StartDate
     , i.EndDate
  from InputData i join Medications m on i.GroupID = m.GroupId
 where i.EventType in ('N', 'C')

And I get the results I want:


Medication table:
MedicationId StartDate               EndDate                 GroupId
------------ ----------------------- ----------------------- -----------
2            2010-01-19 00:00:00.000 2010-01-19 16:28:02.000 5834
1            2008-05-05 00:00:00.000 2008-07-10 00:00:00.000 178248

(2 row(s) affected)

Instructins Table:

InstructionId MedicationId StartDate               EndDate
------------- ------------ ----------------------- -----------------------
15            1            2008-10-02 00:00:00.000 2008-12-23 14:00:47.000
16            1            2009-03-16 00:00:00.000 2009-06-14 00:00:00.000
17            1            2008-05-05 00:00:00.000 2008-07-10 00:00:00.000
18            1            2008-07-10 00:00:00.000 2008-10-02 00:00:00.000
19            2            2010-01-19 00:00:00.000 2010-01-19 16:28:02.000
20            2            2010-03-02 00:00:00.000 2010-03-09 00:00:00.000
21            2            2010-01-26 00:00:00.000 2010-02-25 00:00:00.000

(7 row(s) affected)

But the tables are actually vender tables (I have been tasked with doing the conversion of our old system data into this new system) and I really don't want to change them. I would have to remove the GroupId column when I have completed the insert.

The conversion might be a mutli stage thing and adding/deleting a column from the table could be either inconvenient or not possible. I am sure the application will fail big time if it is run against a modified table.

I have glanced at the output clause but it looks like I can only output data that is also inserted into the table.

So is there a way to do this that does not include modifying the tables.

Thanks

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 12:17:21

you can do it like this


DECLARE  @INSERTED_Medications Table
(
MedicationId int,
StartDate datetime,
EndDate datetime
)

insert into Medications (StartDate,EndDate)
OUTPUT inserted.MedicationID,inserted.StartDate,inserted.EndDate
INTO @INSERTED_Medications 
select i.StartDate
     , i.EndDate
  from InputData i
 where EventType = 'N'


insert into Instructions (MedicationId,StartDate,EndDate)
select MedicationId
     , StartDate
     , EndDate
  from @INSERTED_Medications

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LaurieCox

158 Posts

Posted - 2012-04-26 : 15:24:50

Thank you visakh16 for your response. Unfortunately it does not work.

It only inserts two records into the Instructions table. I need to insert all N(ew) and C(hanged) records from the InputData table into the Instuctions table. With each record linking back to the correct N(ew) record that was inserted into the Medications table.

So the InputData table has these records (added Row number to use as reference below):


Row GroupID     OrderNumber EventType StartDate               EndDate
--- ----------- ----------- --------- ----------------------- -----------------------
01  5834        1           N         2010-01-19 00:00:00.000 2010-01-19 16:28:02.000
02  5834        2           R         2010-01-19 16:28:02.000 2010-01-19 16:57:06.000
03  5834        3           R         2010-01-19 16:57:06.000 2010-01-26 00:00:00.000
04  5834        4           C         2010-01-26 00:00:00.000 2010-02-25 00:00:00.000
05  5834        5           C         2010-03-02 00:00:00.000 2010-03-09 00:00:00.000
06  178248      1           N         2008-05-05 00:00:00.000 2008-07-10 00:00:00.000
07  178248      2           C         2008-07-10 00:00:00.000 2008-10-02 00:00:00.000
08  178248      3           C         2008-10-02 00:00:00.000 2008-12-23 14:00:47.000
09  178248      4           R         2008-12-23 14:00:47.000 2009-03-16 00:00:00.000
10  178248      5           C         2009-03-16 00:00:00.000 2009-06-14 00:00:00.000
11  178248      6           R         2009-06-17 14:19:08.000 2009-09-15 00:00:00.000

I insert the two N(ew) records into the Medications table:


MedicationId StartDate               EndDate                  (group not in table)
------------ ----------------------- -----------------------
1            2008-05-05 00:00:00.000 2008-07-10 00:00:00.000  (178248)
2            2010-01-19 00:00:00.000 2010-01-19 16:28:02.000  (5834)

Note: I don't have GroupId in the original Medications table. So you can't tell which record belongs to which group (I figured it out by looking at the StartDate field for the given data). In the real world there is no column (other than GroupId) that I can link on.

So I need to insert all of the N(ew) and C(hanged) records into Instructions (seven records in all).

The rows 01, 04 and 05 should all have an InstructionId of 2 as they are in GroupId 5834

The rows 06, 07, 09 and 10 should all have an InstructionId of 1 as they are in GroupId 178248

So my expected results are:


InstructionId MedicationId StartDate               EndDate
------------- ------------ ----------------------- -----------------------
<auto gen>    1            2008-10-02 00:00:00.000 2008-12-23 14:00:47.000
<auto gen>    1            2009-03-16 00:00:00.000 2009-06-14 00:00:00.000
<auto gen>    1            2008-05-05 00:00:00.000 2008-07-10 00:00:00.000
<auto gen>    1            2008-07-10 00:00:00.000 2008-10-02 00:00:00.000
<auto gen>    2            2010-01-19 00:00:00.000 2010-01-19 16:28:02.000
<auto gen>    2            2010-03-02 00:00:00.000 2010-03-09 00:00:00.000
<auto gen>    2            2010-01-26 00:00:00.000 2010-02-25 00:00:00.000

My solution was to add GroupId to the Medications table but this is what I don't want to do. But I fear there is no other solution.

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 17:13:39

As i explained in other thread you need to have GroupId/OrderNumber to make sure you're linking correct records

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources