November 12, 2015 at 10:16 am
I am using the following query to update my AF table from SO table.
First issue, in my SO table there is state_id and in AF table I have state such as 'NY', I know what ID corresponds to what state, but how do I embed it in my merge statement? do I have to write 50 case statements?
Second issue, my below merge statement would only replace the name, add, city, zip but would not add missing vendors those have been added to SO, I want to update the existing one as well as add the new ones.
Any advises would be highly appreciated.
MERGE INTO .[dbo].[AF] AS TargetTable
USING (SELECT source_code, name, addr1, city, zip FROM
[DSQL].[DCP].[dbo].[SO]) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
WHEN MATCHED
THEN
UPDATE
SET [TargetTable].[Name] = LEFT ([SourceTable].[name], 30),
[TargetTable].[AddLine1] = [SourceTable].[addr1],
[TargetTable].[City] = [SourceTable].[city],
[TargetTable].[Zip] = [SourceTable].[zip];
November 12, 2015 at 11:31 am
Do you have a "state" table where you can convert the state_id to the state code? If you do your code would look something like this assuming a dbo.state table:
MERGE INTO .[dbo].[AF] AS TargetTable
USING
(
SELECT
SO.source_code,
SO.name,
SO.addr1,
SO.city,
S.state_code,
SO.zip
FROM
[DSQL].[DCP].[dbo].[SO] AS SO JOIN
dbo.State AS S ON SO.state_id = S.state_id
) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
WHEN MATCHED THEN
UPDATE SET
[TargetTable].[Name] = LEFT([SourceTable].[name], 30),
[TargetTable].[AddLine1] = [SourceTable].[addr1],
[TargetTable].[City] = [SourceTable].[city],
[TargetTable].[State] = [SourceTable].[state_code],
[TargetTable].[Zip] = [SourceTable].[zip];
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2015 at 12:13 pm
Thanks Jack, I already had written 50 case statements before your post 😉 your method also works,
Now going on to the second issue, I also want to add the records to my AF table, so far I have this: and it is giving me error:
'Cannot insert the value NULL into column 'Code', table 'S.dbo.AF'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
WHEN NOT MATCHED BY TARGET THEN
INSERT (Code, Name, AddLine1, City, Zip)
VALUES ([SourceTable].[source_code], LEFT ([SourceTable].[name], 30), [SourceTable].[addr1], [SourceTable].[city], [SourceTable].[zip]);
November 12, 2015 at 12:21 pm
Apparently the source_code column in the source table contains NULL values which means you'd never match and also be an insert. You just need to exclude nulls from your source table query:
MERGE INTO .[dbo].[AF] AS TargetTable
USING
(
SELECT
SO.source_code,
SO.name,
SO.addr1,
SO.city,
S.state_code,
SO.zip
FROM
[DSQL].[DCP].[dbo].[SO] AS SO
JOIN dbo.State AS S
ON SO.state_id = S.state_id
WHERE
SO.source_code IS NOT NULL
) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])
WHEN MATCHED THEN
UPDATE SET
[TargetTable].[Name] = LEFT([SourceTable].[name], 30),
[TargetTable].[AddLine1] = [SourceTable].[addr1],
[TargetTable].[City] = [SourceTable].[city],
[TargetTable].[State] = [SourceTable].[state_code],
[TargetTable].[Zip] = [SourceTable].[zip]
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
Code,
Name,
AddLine1,
City,
Zip
)
VALUES (
[SourceTable].[source_code],
LEFT([SourceTable].[name], 30),
[SourceTable].[addr1],
[SourceTable].[city],
[SourceTable].[zip]
);
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2015 at 2:13 pm
Thanks Jack it works !!!!!!!!!!
Also what I need to do with State, now I have to convert state_id into state '03' into 'GA'
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply