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.
| Author |
Topic |
|
Mathias
Posting Yak Master
119 Posts |
 Posted - 2004-02-25 : 05:34:56
|
| I need to insert in a table b each one of the record of the table a 4 times. Just one field inserted should have a case section to differiate which one of the 4 insert it is coming from. Is there a better way then just copying 4 times the same query and modify it?Thanks, Paul |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-25 : 05:42:54
|
| How about having the one query inside a loop that executes four times?Raymond |
 |
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2004-02-25 : 05:54:25
|
| It has one disadvantage, you query 4 times. I was thinking about creating temp table with 4 records (1,2,3,4) and query the two tables but I haven't test it and was wondering if this method is valid. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-25 : 06:08:10
|
| Well you are issue a query four times either way. Can you post the DDL to better see what you're after?Raymond |
|
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2004-02-25 : 07:12:04
|
| In fact I have it working with a second table. In the following example each a record is displayed 4 times and it is faster.create table #TEMP (REC_NUM int)insert into #TEMP (REC_NUM) Select 1union select 2union select 3union select 4select a.FIELD1, t.REC_NUM from Table a, #TEMP t |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-02-25 : 07:37:04
|
| Yes it would be. Is this a practical approach for your real tables in terms of rowsize and row count?Raymond |
|
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2004-02-25 : 08:22:50
|
| Yes it is, the rows in the table a are usually less than 10. In fact a is a openrowset query. What is slow is to ask 4 times the same query through the openrowset. With this method, I use it only once and the code is also easier to read. Thanks for the advice. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-02-25 : 09:31:11
|
| You could search here for Tally or Numbers tables that you could build to help you perform similar functions. |
|
|
|
|
|
|
INSERT 4 TIMES the same record