SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Insert,Update and delete wtout merge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/18/2014 :  07:56:14  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/18/2014 :  09:13:41  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/18/2014 :  12:32:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  16:45:59  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 02/19/2014 :  01:30:09  Show Profile  Reply with Quote
Helo TG ,

Can you me the sample example

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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/19/2014 :  10:08:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000