Copy rows changing details in a table.

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 2008 Forums

Transact-SQL (2008)

Copy rows changing details in a table.

Author

Topic

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2009-07-13 : 03:20:06

Hi,

The question is simply I have a table with this data

TS [DateTime], NAME [Varchar], ID [Identity INT]

The content is something like this

2009-07-01 10:00:00 10203010XF 1
2009-07-01 10:00:05 10203011XF 2
2009-07-01 10:00:10 10203012XF 3

And what I want to do is to expand this information in this way

2009-07-01 10:00:00 10203010XF 1
2009-07-01 10:00:00 10203010XA 4
2009-07-01 10:00:00 10203010XB 5
2009-07-01 10:00:05 10203011XF 2
2009-07-01 10:00:05 10203011XA 6
2009-07-01 10:00:05 10203011XB 7
2009-07-01 10:00:10 10203012XF 3
2009-07-01 10:00:10 10203012XA 8
2009-07-01 10:00:10 10203012XB 9

I mean copy the rows but changing one letter in the field NAME.

I don't want to use a cursor to make it, Is this possible any other way?

Thanks in advance.

The Padrón peppers itch and other don't

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 03:30:02

[code]
DECLARE @sample TABLE
(
TS datetime,
NAME varchar(10),
ID int identity
)

INSERT INTO @sample (TS, NAME)
SELECT '2009-07-01 10:00:00', '10203010XF' UNION ALL
SELECT '2009-07-01 10:00:05', '10203011XF' UNION ALL
SELECT '2009-07-01 10:00:10', '10203012XF'

SELECT *
FROM @sample
/*
TS NAME ID
------------------------------------------------------ ---------- -----------
2009-07-01 10:00:00.000 10203010XF 1
2009-07-01 10:00:05.000 10203011XF 2
2009-07-01 10:00:10.000 10203012XF 3

(3 row(s) affected)
*/

INSERT INTO @sample (TS, NAME)
SELECT TS, STUFF(NAME, LEN(NAME), 1, c.ch)
FROM @sample s
CROSS JOIN
(
SELECT ch = 'A' UNION ALL
SELECT ch = 'B'
) c

SELECT *
FROM @sample

/*
TS NAME ID
------------------------------------------------------ ---------- -----------
2009-07-01 10:00:00.000 10203010XF 1
2009-07-01 10:00:05.000 10203011XF 2
2009-07-01 10:00:10.000 10203012XF 3
2009-07-01 10:00:00.000 10203010XA 4
2009-07-01 10:00:00.000 10203010XB 5
2009-07-01 10:00:05.000 10203011XA 6
2009-07-01 10:00:05.000 10203011XB 7
2009-07-01 10:00:10.000 10203012XA 8
2009-07-01 10:00:10.000 10203012XB 9

(9 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources