INSERT INTO dba.tbl_member Values ( 'USA')
INSERT INTO dba.tbl_member Values ( 'CANADA')
INSERT INTO dba.tbl_member Values ( 'MEXICO')
INSERT INTO dba.tbl_donor Values ( 'USA')
INSERT INTO dba.tbl_donor Values ( 'ASIA')
INSERT INTO dba.tbl_donor Values ( 'BRAZIL')
select * from dba.tbl_member
select * from dba.tbl_donor
-- Now I need to insert all donor_name values from dba.tbl_donor to member_name.tbl_member table.
-- BUT IF the same name already exists in dba.tbl_member then prefix it with 'DL-' and insert else insert the name from 2nd to first table.
-- So here USA exists in both tables so insert 'DL-USA' in dba.tbl_member and then the rest 2 rows.
INSERT INTO dba.tbl_member (member_name)
(SELECT CASE WHEN a.donor_name =b.member_name THEN 'DL-' + substring(a.donor_name, 1, LEN(a.donor_name))
ELSE a.donor_name END AS member_name FROM dba.tbl_donor a,dba.tbl_member b) -- BUT I THINK MY THIS QUERY NEEDS YOUR HELP
FROM dba.tbl_donor
So for this scenario I should be able to see total 6 rows in dba.tbl_member after insert.
Microsoft SQL ServerSybase DatabaseMySQL Server
Last Comment
Anthony Perkins
8/22/2022 - Mon
TempDBA
You can use outer join for this.
INSERT INTO dba.tbl_member (member_name)
SELECT
CASE WHEN b.member_name is null
THEN 'DL-' + a.donor_name
ELSE a.donor_name
END AS member_name
FROM dba.tbl_donor a
left outer join dba.tbl_member b
on a.donor_name = b.member_name
cottage125
ASKER
ITs giving wrong results see below, thats now what I want.
member_name
'DL-ASIA'
'DL-BRAZIL'
'USA'
But I want,
member_name
'ASIA'
'BRAZIL'
'DL-USA'
ralmada
try
INSERT INTO dba.tbl_member (member_name)
select case when b.member_name is null then a.donor_name else 'DL -' + a.donor_name end
FROM dba.tbl_donor a
left outer join dba.tbl_member b on a.donor_name = b.member_name
<<-- BUT IF the same name already exists in dba.tbl_member then prefix it with 'DL-' and insert else insert the name from 2nd to first table.
-- So here USA exists in both tables so insert 'DL-USA' in dba.tbl_member and then the rest 2 rows.>>
I thought that's what you wanted. Anyway, Just reverse the case statemetn
INSERT INTO dba.tbl_member (member_name)
SELECT
CASE WHEN b.member_name is not null
THEN 'DL-' + a.donor_name
ELSE a.donor_name
END AS member_name
FROM dba.tbl_donor a
left outer join dba.tbl_member b
on a.donor_name = b.member_name
ralmada
or even simpler
INSERT INTO dba.tbl_member (member_name)
select coalesce('DL -' + b.member_name, a.donor_name)
FROM dba.tbl_donor a
left outer join dba.tbl_member b on a.donor_name = b.member_name
cottage125
ASKER
Okay Thats but is there any way I can use if exist or someting like that and dont use any joins?? Just wondering.
INSERT INTO dba.tbl_member (member_name)
SELECT
CASE WHEN b.member_name is null
THEN 'DL-' + a.donor_name
ELSE a.donor_name
END AS member_name
FROM dba.tbl_donor a
left outer join dba.tbl_member b
on a.donor_name = b.member_name