Inserting multiple fields into one field

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

SQL Server Development (2000)

Inserting multiple fields into one field

Author

Topic

dshelton
Yak Posting Veteran

73 Posts

Posted - 2001-11-26 : 19:48:28

I am using a cursor which grabs each field from this table:

code Column1 Column2 Column3
123 A B C

And turns it into this:

code Column ColumnName
123 A Column1
123 B Column2
123 C Column3

Is there a better way to do this??

berinder
Starting Member

18 Posts

Posted - 2001-11-27 : 04:00:30

hmm, if you know the column names you can use a union...

insert into table2 (code, column, columnName)
Select code, column1, 'column1'
union
select code, column2, 'column2'
union
select code, column3, 'column3'

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-11-27 : 05:29:26

Slightly better to use UNION ALL: even SQL Server cannot (yet) optimize unions that are guaranteed disjoint by a distinct literal tag.

It can be done with one scan of the main table like this:


SELECT
  Code,
  CASE Cols.C
    WHEN 'Column1' THEN Column1
    WHEN 'Column2' THEN Column2
    WHEN 'Column3' THEN Column3
  END,
  Cols.C
FROM DataTable
CROSS JOIN (
  SELECT 'Column1' AS C
  UNION ALL SELECT 'Column2'
  UNION ALL SELECT 'Column3') Cols

But it's unlikely the performance will be usefully better unless the data table is big and slow to access. It gave about the same performance when I tried it on an 80000 row table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources