Copying a table to another table: avoiding Null

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

Transact-SQL (2000)

Copying a table to another table: avoiding Null

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-06-25 : 06:20:22

Hi All,

I'm trying to copy table1 to table2. I'd like to copy into the second table with out ending up with lots of nulls.

This needs to be fast!

Thanks! Tim

Table 1:

Declare @allNamesTable Table
(person_id int
, names varchar(60)
, namepart varchar(60)
, aggregate_role varchar(50)
, starting_position int
, lPos int
, recordchanged tinyint)

namepart stores 'surname', 'legal surname' etc.

Table 2:

Declare @allNamesPivotTable Table
( person_id int
, surname varchar(60)
, legal_surname varchar(60)
, forename varchar(60)
, chosen_forename varchar(60)
, midname varchar(60)
, aggregate_role varchar(50)
, starting_position int
, lPos int
, recordchanged tinyint)

new table is replaces the namepart field in the first for actual fields.

I'd like to copy into the second table with out ending up with lots of nulls e.g.

Table1:
person_id names namepart aggregate_role starting_position lPos recordchanged
----------- ---------- ---------------------- ----------- ----------------- ----------- -------------
100 Abell legal_surname 65560 0 0 NULL
100 Abell surname 65560 0 0 1
100 Anita forename 65560 0 0 1
100 Anita chosen_forename 65560 0 0 NULL

Table2: NOT WANTED! Just one record with names in if possible....
person_id surname legal_surname forename chosen_forename midname aggregate_role starting_position lPos recordchanged
----------- -------------- ----------------------- --------------- --------------------------- ----------------- ----------- -------------
100 NULL Abell NULL NULL NULL 65560 0 0 NULL
100 Abell NULL NULL NULL NULL 65560 0 0 1
100 NULL NULL Anita NULL NULL 65560 0 0 1
100 NULL NULL NULL Anita NULL 65560 0 0 NULL

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-06-25 : 06:25:32

RE-DONE TABLES FOR CLARITY!
Table1:
person_id names namepart aggregate_role starting_position lPos recordchanged
----------- ---------- ---------------------- ----------- ----- ------------ ----------- -------------
100 Abell legal_surname 65560 0 0 NULL
100 Abell surname 65560 0 0 1
100 Anita forename 65560 0 0 1
100 Anita chosen_forename 65560 0 0 NULL

person_id surname legal_surname forename chosen_forename midname aggregate_role starting_position lPos recordchanged
----------- -------------- ----------------------- --------------- --------------------------- ----------------- ----------- -
100 NULL Abell NULL NULL NULL 65560 0 0 NULL
100 Abell NULL NULL NULL NULL 65560 0 0 1
100 NULL NULL Anita NULL NULL 65560 0 0 1
100 NULL NULL NULL Anita NULL 65560 0 0 NULL

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-06-25 : 06:26:27

Or maybe not! Text formatting on this forum is annoying!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 07:00:31

put
[ c o d e ]
[ / c o d e ]

around the text to preserve formatting

insert table2 (person_id, surname, legal_surname, ...)
select person_id ,
(select names from table1 t2 where t1.person_id = t2.person_id and t2.namepart = 'surname') ,
(select names from table1 t2 where t1.person_id = t2.person_id and t2.namepart = 'legal_surname') ,
....
from (select distinct person_id from table1) t1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2004-06-25 : 07:25:18

Thanks for the solution....

unfortunatly it just added 40 seconds to a 5 second script!!!

any other ideas?

Thanks, Tim

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 07:34:20

I take it emp_id, namepart is indexed.

try
select t1.emp_id, t1.names, t3.names, ....
from table1 t1
join table1 t2
on t1.person_id = t2.person_id and t2.namepart = 'legal_surname'
join table1 t3
on t1.person_id = t3.person_id and t3.namepart = forename'
where t1.namepart = 'surname'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources