Re: create and/or insert into a table, specifying columns once
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/26/04
- Next message: Hugo Kornelis: "Re: Default value problem"
- Previous message: Ian Frawley: "Re: SQL Server monitoring"
- In reply to: David W. Rogers: "Re: create and/or insert into a table, specifying columns once"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Aug 2004 10:57:52 +0200
On Wed, 25 Aug 2004 17:07:09 -0700, David W. Rogers wrote:
>
>Moving the "SELECT INTO" out of the loop doesn't gain me anything - I still
>have to create a query that comes up with all of the columns in two places:
>first to create a new table and then to populate an existing table.
>
>The problem is that the syntax:
> SELECT select_list [ INTO new_table ]
>
>is the only way to use a select list to populate a table that doesn't
>already exist. And INSERT INTO requires a pre-existing table. What I would
>need would be:
>
> INSERT [INTO] new_table derived_table
>
>Thanks,
>David
Hi David,
I'm sorry, but there is no SQL command that does something like this. If
you really need to build your result set row by row, then your options
are:
1. Use a permanent table. Create it once; delete everything from it before
(or after) use. The quickest way to empty a table is to use TRUNCATE.
2. Use CREATE TABLE for a temporary table in your procedure. This will
create the table before the loop; you can now use INSERT INTO .. SELECT
for all iterations.
3. Use a dummy SELECT .. INTO (with dummy, I mean that you add WHERE 1=2
to ensure that you get only the structure, not the data) before the loop;
this duplicates a lot of your statement but not necessarily all.
4. Leave your code as it is now.
If the result set MUST be built row by row, I'd prefer to use the first of
these options. If that is not possible (e.g. because multiple users can
run the same procedure at the same time), choose the second option
instead. I see no reason to prefer the third or fourth option over this.
But as I said: that's only if you must do it row by row. In the example
code you posted, there is no reason at all to use iteration. Instead, you
could use a numbers table (creating it is a one-time operation and you'll
benefit from it very often) - see http://www.aspfaq.com/show.asp?id=2516.
Once you have your numbers table, you can replace the complete code you
posted in the message that started this discussion with this single query:
SELECT
numbers.n AS Number1,
GETDATE() AS DateTime,
t1.*,
t2.ATextColumn SecondText,
t2.AnotherTextColumn ThirdText
FROM #Table1 t1
JOIN #Table2 t2
ON t1.ID = t2.ID
JOIN numbers
ON t1.ID = numbers.n
WHERE numbers.n BETWEEN 0 AND 10
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Hugo Kornelis: "Re: Default value problem"
- Previous message: Ian Frawley: "Re: SQL Server monitoring"
- In reply to: David W. Rogers: "Re: create and/or insert into a table, specifying columns once"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
