July 24, 2003 at 12:42 am
Good morning everybody.
I have a question for you
i have a table with the following fields
id field1 field2 field3 field4 field5
1 a b c d e
how can i insert into a new table the values like
id field
1 a
1 b
1 c
1 d
1 e
I dont want to use cursors or union select.
Thanks in advance.
July 24, 2003 at 5:30 am
I solved a similar problem a while back using DTS in conjuction with ActiveX scripting. Is this an option for you?
Joseph
July 24, 2003 at 5:35 am
Yes why not? Let me check it.
Thanks.
July 24, 2003 at 7:45 am
You might try a simple insert with a union statement like so:
set nocount on
create table old_table(id int, field1 char(1), field2 char(1), field3 char(1), field4 char(1), field5 char(1))
create table new_table (id int, field char(1))
insert into old_table values(1,'a','b','c','d','e')
insert into new_table select id, field1 from old_table
union
select id, field2 from old_table
union
select id, field3 from old_table
union
select id, field4 from old_table
union
select id, field5 from old_table
select * from new_table
drop table new_table, old_table
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 24, 2003 at 7:49 am
I've already done it using union and cursor but is there any other faster way to do so ?
Thank you.
July 24, 2003 at 9:58 am
Guess I should have read your original post a little closer. Here is another way that creates a flat file and then loads using bulk insert. I tested this and the union query against 100,000 records in old_table, and found that the bulk insert did perform faster.
exec xp_cmdshell 'osql -r -h-1 -Sesp--gal0303 -E -Q"set nocount on;select cast(id as char(5)) + '','' + rtrim(field1) + char(13) + char(10) + cast(id as char(5)) + '','' + rtrim(field2) + char(13) + char(10) +cast(id as char(5)) + '','' + rtrim(field3) + char(13) + char(10) +cast(id as char(5)) + '','' + rtrim(field4) + char(13) + char(10) + cast(id as char(5)) + '','' + rtrim(field5) from old_table" -oC:\temp\test.txt'
BULK INSERT new_table FROM 'c:\temp\test.txt' with (FIELDTERMINATOR=',')
[/quote]
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 25, 2003 at 5:24 am
declare @tbla table(keyid int , field1 char(1) ,field2 char(1) ,field3 char(1),field4 char(1),field5 char(1) )
insert into @tbla values (1,'a','b','c','d','e')
declare @tblnum table (number int)
insert into @tblnum values (1)
insert into @tblnum values (2)
insert into @tblnum values (3)
insert into @tblnum values (4)
insert into @tblnum values (5)
select keyid,
case number
when 1 then field1
when 2 then field2
when 3 then field3
when 4 then field4
when 5 then field5
end
from @tblnum cross join @tbla
M A Srinivas
M A Srinivas
July 25, 2003 at 3:18 pm
Assuming the denormalized columns are exclusive (which seems to be the case based upon the table schema) use the UNION ALL command. The UNION command alone will cause a lot of extra overhead because it will create a temp table to eliminate duplicates - which BTW may also give erroneous results if duplicates are desirable.
I believe but I am not sure, that the table will have to be read as many times as the number of columns you will be accessing if you use either UNION command. If physical I/O is a bottleneck for you, then a cursor might not be a bad idea in this case.
In Query Analyzer, use SET STATISTICS IO ON and SET STATISTICS TIME ON, and try the various approaches in Query Analyzer. Comparing the operational performance from these indicators will tell the tale.
Hope this helps,
Bill
Bill Bertovich
bbertovich@interchangeusa.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply