March 25, 2015 at 10:54 am
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?
March 25, 2015 at 11:34 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 25, 2015 at 11:41 am
Got it.
Mucho thanks Phil.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply