Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Insert,Update and delete wtout merge

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-02-18 : 07:56:14
How to Insert,Update and delete through script without using merge.

I have simple requirement of Deleting,Inserting and Update from one table to other table

Here is the Sample Data

CREATE  TABLE #Table1  
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)

CREATE TABLE #Table2
(ID INT ,Name VARCHAR(30),DATEKEY INT)


My query :

INSERT INTO #Table2(ID,Name,DATEKEY)
Select ID,Name,DATEKEY From #Table1

My Result will be like this :

ID	Name	DATEKEY
1 Mohan 20131231
2 Raj 20131231
3 Majja 20131231
4 Majjaa 20131231


So now i need to update 1st record and add another new record

So i need to update as well as delete the existing data in Target table.
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)

My output should come like this one :

ID	Name	DATEKEY
1 Mohan 20131231
5 Macha 20131231

with out using merge how can i handle Update,insert and Delete through TSQL

P.V.P.MOhan

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2014-02-18 : 09:13:41
How does your delete rule work?

Updating and inserting is easy, If key exists, Update set... where ID = inserted.ID

Insert is also easy, either use NOT EXISTS or LEFT JOIN where key field is null.

Delete as I say is more difficult unless you are passing a field to tell you whether it is a delete or not, if not, what are your delete rules?

Also, why do you not want to use Merge?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-18 : 12:32:13
how did you delete last 3 records? what was the basis for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 16:45:59
MERGE is generally used to make your target table have the same rows as your source table. If that is your objective? If so you can use 3 separate statements (insert where not exists in target, update where different, and delete where not exists in source). Let us know if that is what you want but you don't know the sql for it. You could also use replication to keep the target like the source.

I'll echo the others' question about delete rules if your "my output should come like this one" is actually correct.

Be One with the Optimizer
TG
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-02-19 : 01:30:09
Helo TG ,

Can you me the sample example

P.V.P.MOhan
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-19 : 10:08:17
quote:
Originally posted by mohan123

Helo TG ,

Can you me the sample example

P.V.P.MOhan


Sure - here's one way to do it:

--update where different
update t2 set
t2.name = t1.name
,t2.datekey = t1.datekey
from #table1 t1
inner join #table2 t2 on t2.id = t1.id
where not
( t2.name = t1.name
and t2.datekey = t1.datekey)

--delete target where not in source
delete t2
from #table2 t2
left outer join #table1 t1
on t1.id = t2.id
where t1.id is null

--insert target where not in target
insert #table2 (id, name, datekey)
select t1.id
,t1.name
,t1.datekey
from #table1 t1
left outer join #table2 t2
on t2.id = t1.id
where t2.id is null


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -