Insert into SELECT with composite primary key failing SQL Server 2008
I have one very large, un-normalized table which I am in the process of fixing. From that large table I'm normalizing the data. I used the SQL statement
INSERT INTO smallTable(patientID, admissionDate, dischargeDate) select distinct patientID, admissionDate, dischargeDate FROM largeTable
So my smallTable is populated with the correct number of rows. There's another column, drgCode that I want to add to my smallTable. I tried the following query to do that
INSERT INTO smallTable(drgCode) select drgCode from ( SELECT DISTINCT patientID, admissionDate, dischargeDate, drgCode from largeTable) as t
The error I was given reads cannot insert the value NULL into patientID, column does not alloq nulls, insert fails.
The only way that the drgCode will be chosen correctly is if some variant of the select distinct query is used. How can I insert only one field, when the other fields must be included to narrow down the search.
I know I could do this if I emptied out my smallTable, but I figured there's gotta be a way around it.
with drg as (SELECT DISTINCT patientID, admissionDate, dischargeDate, drgCode from largeTable) update s set s.drgCode = l.drgCode from smallTable s join drg l on s.patientId = l.patientId and s.admissionDate = l.admissionDate and s.dischargeDate = l.dischargeDate
As per my understanding, if you have "PatientID" to be unique in both the tables, you can do something like below.
Update S SET S.drgCode = L.drgCode FROM SmallTable S INNER JOIN LargeTable T ON S.PatientID = T.PatientID
Hope this Helps!!
When you perform an insert to a table, any values not specified in the query are poulated with the default value for the column. If there is no default value on the column, NULL will be used. You recieved that particular error message because your column does not allow NULL and does not have a default.
Given your reply to Praveen, perhaps you should be further normalizing and put the drgCodes into a separate table.