how to transfer row - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to transfer row

Author

Topic

masteripper
Starting Member

25 Posts

Posted - 2009-04-15 : 08:06:36

Hi to everybody
I have a situation and i would like some help
I have a table and i want to transfer a row.
Let me describe it
Table X
FIELD1(PK) FIELD2 FIELD3
x01............y01............z01
x02............y02............z02
x03............y03............z03

So lets assume i want to get the data from 2nd line ( except PK ...these will be y02 ,z03) and make a new row that will be like this:
x04 y02 z02
and delete 2nd line (x02..y02...z02)
I know how to insert the data and delete them but i don't have how to do both.
Given the fact that the table is a little more complex and i want to make a stored procedure a flexible - generic solution would ne great.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 08:10:29

You can use DELETE with the OUTPUT operator to insert back into same table.

E 12°55'05.63"
N 56°04'39.26"

masteripper
Starting Member

25 Posts

Posted - 2009-04-15 : 08:15:55

Thanks man ....so simple
I have to read more...:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 08:16:52

[code]DECLARE @Sample TABLE
(
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10)
)

INSERT @Sample
SELECT 'X01', 'Y01', 'Z01' UNION ALL
SELECT 'X02', 'Y02', 'Z02' UNION ALL
SELECT 'X03', 'Y03', 'Z03' UNION ALL
SELECT 'X04', 'Y04', 'Z04'

SELECT *
FROM @Sample

DELETE
FROM @Sample
OUTPUT 'X05',
deleted.Col2,
deleted.Col3
INTO @Sample
WHERE Col1 = 'X02'

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

masteripper
Starting Member

25 Posts

Posted - 2009-04-15 : 08:36:40

thanks again man.Probably u saved me from a couple of lost hours.....
Time to read more (or watch more movies...:))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources