Create 4 records from 1 - 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 2000 Forums

Transact-SQL (2000)

Create 4 records from 1

Author

Topic

Tigger
Yak Posting Veteran

85 Posts

Posted - 2003-01-30 : 18:45:59

I have a table of 2 million records and for each record in this table I need to insert 4 records into another table as shown:

eg (using sample data)
table 1
id_value field1 field2 field3 field4 field5 field6
1 11 21 31 41 51 61
2 12 22 32 42 52 62
3 13 23 33 43 53 63
4 14 24 34 44 54 64

creates
table2
id_value text_field fieldA fieldB
1 AAA 11 21
2 AAA 12 22
3 AAA 13 23
4 AAA 14 24
1 BBB 31 41
2 BBB 32 42
3 BBB 33 43
4 BBB 34 44
1 CCC 0 51
2 CCC 0 52
3 CCC 0 53
4 CCC 0 54
1 DDD 0 61
2 DDD 0 62
3 DDD 0 63
4 DDD 0 64

so in effect
1st row created uses field1 and field2
2nd row created uses field3 and field4
3rd row created uses 0 and field5
4th row created uses 0 and field6

Make sense ???

I've tried 3 different way so far:

A - 4 separate insert statements, one for each row type
B - one insert statement with 4 select clauses union-ed together
C - while loop containing insert statement and looping 4 times

Just running the select statement
Approach A takes 6 minutes
Approach B takes 7 minutes
Approach C takes 8 minutes

Anybody have any other ways of doing this which would be quicker?
We are converting all our data from existing system to a new system
and this type of processing is used all over the place

Sample data for you:
CREATE TABLE sqltest
(id_value integer, field1 int, field2 int, field3 int, field4 int, field5 int, field6 int)

insert into sqltest
values
(1,11,21,31,41,51,61)

insert into sqltest
values
(2,12,22,32,42,52,62)

insert into sqltest
values
(3,13,23,33,43,53,63)

insert into sqltest
values
(4,14,24,34,44,54,64)

CREATE TABLE sqltest2
(id_value int, text_field varchar(4), fieldA int, fieldB int)

insert into sqltest2
SELECT id_value, 'AAA', field1, field2
FROM sqltest

insert into sqltest2
SELECT id_value, 'BBB', field3, field4
FROM sqltest

insert into sqltest2
SELECT id_value, 'CCC', 0, field5
FROM sqltest

insert into sqltest2
SELECT id_value, 'DDD', 0, field6
FROM sqltest

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-30 : 19:52:42

You could also try 4 bcp out statements and bcp the data into the new table.
Make sure that it is not logged and could be quickest.

==========================================
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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-31 : 07:37:53

Use a cross join; this is the easiest way to "uncross-tab" a table into a longer, skinnier, table.

Here's an example:

SELECT YourKey,
CASE a WHEN 1 THEN Field1 WHEN 2 THEN FIeld2 WHEN 3 THEN Field3 ELSE Field4 End as Field
FROM
YourTable
CROSS JOIN
(SELECT 1 as A UNION ALL
SELECT 2 as A UNION ALL
SELECT 3 as A UNION ALL
SELECT 4 as A) B

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-31 : 12:09:21

How About This as another option:

Create View v_sqltest3 As
SELECT id_value, 'AAA' As text_field, field1 As FieldA, field2 As FieldB
FROM sqltest
Union All
SELECT id_value, 'BBB' As text_field, field3 As FieldA, field4 As FieldB
FROM sqltest
Union All
SELECT id_value, 'CCC' As text_field, 0 As FieldA, field5 As FieldB
FROM sqltest
Union All
SELECT id_value, 'DDD' As text_field, 0 As FieldA, field6 As FieldB
FROM sqltest
GO

Then the insert is:

insert into sqltest4 (id_value, text_field, fieldA, fieldB)
SELECT id_value, text_field, fieldA, fieldB
FROM v_sqltest3

I have to check, but I'm hoping the optimizer will take advantage of parallelism...let you know

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources