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.
March 25, 2015 at 11:41 am
Got it.
Mucho thanks Phil.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply