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.

Answers


    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.


Need Your Help

JTextField in GridLayout

java swing jtextfield layout-manager grid-layout

I am having a problem with my text fields in my gui taking up the whole grid section in my custNamePanel(). I was wondering how I can fix that. My info panel seems to be just fine with its JTextFie...

I want to know the time taken by an action in jsp. how to do that?

javascript jsp-tags

<a onclick="javascript:submitForm('save.htm');">