Merge ito different table - SQL Server Forums

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

Merge ito different table

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2012-09-18 : 06:18:09

DECLARE @t AS table(id int)
DECLARE @tbltest as table (Id int, name varchar(100))

INSERT INTO @tbltest VALUES(1,'RIJU'),(2,'Anu')

MERGE @tbltest AS TARGET
USING ( SELECT 1,'SONY' )
as Source(Id,name)
on ( target.name=source.name )
when matched then
--update set name='SONY'
here i need to insert into @t

when not matched then
INSERT (id,name)
values (3,'SONY') ;

SELECT * FROM @tbltest
SELECT * FROM @t

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-18 : 06:59:52

Hi aoriju,
Check below code.. I hope it will help you

DECLARE @t AS table(id int)
DECLARE @tbltest as table (Id int, name varchar(100))

INSERT INTO @tbltest VALUES(1,'RIJU'),(2,'Anu')

MERGE @tbltest AS TARGET
USING ( SELECT 1,'SONY' )
as Source(Id,name)
on ( target.name=source.name )
when matched then
update set name='SONY'
--here i need to insert into @t

when not matched then
INSERT (id,name)
values (3,'SONY')

OUTPUT deleted.id
INTO @t;

SELECT * FROM @tbltest
SELECT * FROM @t

--
Chandu

aoriju
Posting Yak Master

156 Posts

Posted - 2012-09-18 : 08:12:48

Not helped me

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-18 : 08:21:49

hi,
What do you want here. you need to insert id that is being updated (in tbltest)??
Once execute this code.. you will get id ( i.e. 2) into @t table

DECLARE @t AS table(id int)
DECLARE @tbltest as table (Id int, name varchar(100))

INSERT INTO @tbltest VALUES(1,'RIJU'),(2,'Anu')

MERGE @tbltest AS TARGET
USING ( SELECT 1,'Anu' )
as Source(Id,name)
on ( target.name=source.name )
when matched then
update set name='SONY'
--here i need to insert into @t

when not matched then
INSERT (id,name)
values (3,'SONY')

OUTPUT deleted.id
INTO @t;

SELECT * FROM @tbltest
SELECT * FROM @t

-------------
If it doesn't work, please explore your requirement

--
Chandu

aoriju
Posting Yak Master

156 Posts

Posted - 2012-09-18 : 09:19:13

i want to insert some randum id's into @t ....i mean some 1000 or something

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-18 : 09:24:08

Try this one

OUTPUT rand()*1000
INTO @t;

--
Chandu

aoriju
Posting Yak Master

156 Posts

Posted - 2012-09-18 : 09:29:25

Hi bandi..ur statement is correct..but i need to insert some values based on matching case

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-18 : 09:53:05

Hi,
Here am getting id of mathced row and then applying some logic [ i.e. (deleted.id)*1000 ]

when matched then
update set name = target.name

OUTPUT (deleted.id)*1000
INTO @t;

Check this

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources