September 29, 2016 at 8:54 am
CREATE TABLE Temp
(
ContactID INT,
FirstName NVARCHAR(100),
MiddleName NVARCHAR(100),
LastName NVARCHAR(100)
)
INSERT INTO Temp
(1.'Dan','K','J')
INSERT INTO Temp
(2.'Lary','K'.'L')
INSERT INTO Temp
(3.'Bin'.'K'.'L')
INSERT INTO Temp
(4.'J','J','L')
CREATE TABLE Main
(
ContactID INT,
FirstName NVARCHAR(100),
MiddleName NVARCHAR(100),
LastName NVARCHAR(100)
)
INSERT INTO Main
(1.'Dan','K','J')
Requirement.
Right now contactID 1 is now in Temp and Main table .
Now i need to write in such a way If any id id that is existed in temp and main table.Delete the records from main table and insert the records from the temp table to main table
for example ContacID 1 is existed in Main and Temp. Need to delete the records fromthe main table and again insert all the records from the temp table
September 29, 2016 at 9:53 am
Does this help? The code is based on your sample data above, which by the way, has syntax errors I will leave for you to find and correct.
print 'Display data in Main that exists in Temp';
select
*
from
dbo.Main m
where
exists(select 1 from dbo.Temp t where t.ContactID = m.ContactID)
print 'Delete the data in Main that exists in Temp';
with base as (
select
*
from
dbo.Main m
where
exists(select 1 from dbo.Temp t where t.ContactID = m.ContactID)
)
delete from base;
print 'Display data in Main that exists in Temp, should not be any now.';
select
*
from
dbo.Main m
where
exists(select 1 from dbo.Temp t where t.ContactID = m.ContactID)
print 'Insert data from Temp into Main';
insert into dbo.Main(ContactID, FirstName, MiddleName, LastName)
select ContactID, FirstName, MiddleName, LastName
from dbo.Temp;
print 'Show data in Main.';
select * from dbo.Main;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply