Cannot insert the value NULL into column

• krypto69

  SSChampion

  Points: 13604

  More actions

  March 25, 2015 at 10:54 am

  #316529

  Hi,

  I have the query below to move data from database A table A to database B table B. Both DB's are similar in schema.

  This is the statement:

  SELECT

  provider_ud = REPLACE(pr.TaxIDNumber, '-','') + '_' + LEFT(p.FirstName, 3) + LEFT(p.LastName,3)

  ,last_name = p.LastName

  ,first_name = p.FirstName

  ,middle_name = p.MiddleName

  ,name_prefix = px.PrefixName

  ,name_suffix = sx.SuffixName

  ,dob = p.BirthDate

  ,gender = CASE WHEN p.GenderID = 1 THEN 'M' ELSE 'F' END

  ,language_id = 3 -- Default to English

  ,pend_claims = 0 -- Default to 0

  ,currency_id = 1 -- Default to USD

  FROM Practitioners p

  LEFT JOIN Prefixes px

  ON p.PrefixID = px.PrefixID

  LEFT JOIN Suffixes sx

  ON p.SuffixID = sx.SuffixID

  INNER JOIN PractitionerLocations pl

  ON pl.PractitionerID = p.PractitionerID

  INNER JOIN PracticeLocations prl

  ON prl.LocationID = pl.LocationID

  INNER JOIN Practices pr

  ON pr.PracticeID = prl.PracticeID

  --WHERE p.ChangedOn > '3/18/15' -- Need to determine frequency of transfer

  WHERE (REPLACE(pr.TaxIDNumber, '-','') + '_' + LEFT(p.FirstName, 3) + LEFT(p.LastName,3)) is not null

  GROUP BY REPLACE(pr.TaxIDNumber, '-','') + '_' + LEFT(p.FirstName, 3) + LEFT(p.LastName,3), p.LastName, p.FirstName, p.MiddleName, px.PrefixName

  ,sx.SuffixName, p.BirthDate, p.GenderId

  I keep getting an error "Cannot insert the value NULL into column "Provider_ID".

  Now I realize there is not a provider_ID column in my query. But I thought if I checked 'insert identity values" it would automatically insert the provider_id for me on the insert (it's the pkey). But all I get is the error. What am I missing?

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  March 25, 2015 at 11:34 am

  #1786386

  If Provider_Id is an IDENTITY column and you want to generate new values in it, try un-setting 'Insert Identity values'.

  This setting is used where you want to override the identity setting of the column with your own provided values.

  ---

• krypto69

  SSChampion

  Points: 13604

  More actions

  March 25, 2015 at 11:41 am

  #1786391

  Got it.

  Mucho thanks Phil.