one table where I am having trouble.
I normally do a copy like so:
INSERT table2 (field1, field2)
SELECT field1, field2 FROM table1
This works fine most of the time where I take all the data from table 1 and
put it in table 2.
My problem is that I have one table that has a version number that is only
in table 1. Normally, I would just do this in that case:
INSERT table2 (field1, field2, version)
SELECT field1, field2, 0 FROM table1
But what I need is to be able to look at the version in table2 and if null
(first time) then it is a 0, if there is a version already there, add 1 to
the max version number. Something like:
INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
SELECT field1, field2, 0 FROM table1
The problem is the version is in table 2 and not table1 (where I am doing my
select from).
How would I do this? Would I do a select inside of the insert?
Thanks,
TomI got it.
INSERT table2 (field1, field2, version)
SELECT field1, field2, isnull((select max(version) from table2),-1)+1 FROM
table1
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yC02Tu5FHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a lot of templates that I copy from table to another, but ran into
>one table where I am having trouble.
> I normally do a copy like so:
> INSERT table2 (field1, field2)
> SELECT field1, field2 FROM table1
> This works fine most of the time where I take all the data from table 1
> and put it in table 2.
> My problem is that I have one table that has a version number that is only
> in table 1. Normally, I would just do this in that case:
> INSERT table2 (field1, field2, version)
> SELECT field1, field2, 0 FROM table1
> But what I need is to be able to look at the version in table2 and if null
> (first time) then it is a 0, if there is a version already there, add 1 to
> the max version number. Something like:
> INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
> SELECT field1, field2, 0 FROM table1
> The problem is the version is in table 2 and not table1 (where I am doing
> my select from).
> How would I do this? Would I do a select inside of the insert?
> Thanks,
> Tom
>|||INSERT adds rows to a table, yet you're describing an update. So, which is i
t?
Please post DDL and sample data.
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:F309F0BE-3466-46B0-8736-515DF8B54C37@.microsoft.com...
> INSERT adds rows to a table, yet you're describing an update. So, which is
> it?
No it is an insert.
I am copying records from another table (which is a template).
When I need a new set of records I copy them from the template. The
template is where all changes are made.
Tom
> Please post DDL and sample data.
>
> ML|||Actually I thought I thought I had the answer (and I do), but only by
accident. It doesn't work as I expect it should.
Here is the DDL and Samples:
DROP TABLE table1
DROP TABLE table2
CREATE TABLE table1 (field1 Int,field2 Int)
CREATE TABLE table2 (field1 Int,field2 Int,version Int)
INSERT table1 values(15,20)
INSERT table1 values(15,21)
INSERT table1 values(15,22)
The inserts are:
INSERT table2 (field1,field2,version)
SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2)
FROM table1
SELECT * FROM table2
On the first run table2 looks like:
field1 field2 version
-- -- --
15 20 0
15 21 0
15 22 0
on the second run it looks like:
field1 field2 version
-- -- --
15 20 0
15 21 0
15 22 0
15 20 1
15 21 1
15 22 1
This is actually what I wanted, but I am
as to why it does this.When I look at the statement I thought I made a mistake and thought I would
have to get the max(version) before I did the insert/select as I expected
the result to be:
field1 field2 version
-- -- --
15 20 0
15 21 1
15 22 2
15 20 3
15 21 4
15 22 5
But what seems to be happening is that the inner select (the one that gets
the maximum version) is only being executed once for the whole command
instead of for each record inserted.
Is that what is happening?
Thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eaPxHBv5FHA.3416@.TK2MSFTNGP15.phx.gbl...
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:F309F0BE-3466-46B0-8736-515DF8B54C37@.microsoft.com...
> No it is an insert.
> I am copying records from another table (which is a template).
> When I need a new set of records I copy them from the template. The
> template is where all changes are made.
> Tom
>|||On Fri, 11 Nov 2005 09:25:49 -0800, tshad wrote:
>I have a lot of templates that I copy from table to another, but ran into
>one table where I am having trouble.
>I normally do a copy like so:
>INSERT table2 (field1, field2)
>SELECT field1, field2 FROM table1
>This works fine most of the time where I take all the data from table 1 and
>put it in table 2.
>My problem is that I have one table that has a version number that is only
>in table 1. Normally, I would just do this in that case:
>INSERT table2 (field1, field2, version)
>SELECT field1, field2, 0 FROM table1
>But what I need is to be able to look at the version in table2 and if null
>(first time) then it is a 0, if there is a version already there, add 1 to
>the max version number. Something like:
>INSERT table2 (field1, field2, version = IsNull(Max(version),-1)+1)
>SELECT field1, field2, 0 FROM table1
>The problem is the version is in table 2 and not table1 (where I am doing m
y
>select from).
>How would I do this? Would I do a select inside of the insert?
Hi Tom,
Probably something like this:
INSERT INTO table2 (field1, field2, version)
SELECT t1.field1, t1.field2,
(SELECT COALESCE(MAX(t2.version) + 1, 0)
FROM table2 AS t2
WHERE t2.field1 = t1.field1
AND t2.field2 = t1.field2)
FROM table1 AS t1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 11:18:02 -0800, tshad wrote:
(snip)
>When I look at the statement I thought I made a mistake and thought I would
>have to get the max(version) before I did the insert/select as I expected
>the result to be:
>field1 field2 version
>-- -- --
>15 20 0
>15 21 1
>15 22 2
>15 20 3
>15 21 4
>15 22 5
>But what seems to be happening is that the inner select (the one that gets
>the maximum version) is only being executed once for the whole command
>instead of for each record inserted.
>Is that what is happening?
Hi Tom,
No. The subquery IS executed for each row (at least in theory - SQL
Server can -and will!- optimize, as long as it doesn't affect the
results).
What happens is that all the subqueries are executed against a "before"
image of the table. That is done because data modifications are supposed
to be "instanteneous" - all rows are added to the table at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:0hdan11rc8q2b013305tdk0dq0l65o6ic9@.
4ax.com...
> On Fri, 11 Nov 2005 11:18:02 -0800, tshad wrote:
> (snip)
> Hi Tom,
> No. The subquery IS executed for each row (at least in theory - SQL
> Server can -and will!- optimize, as long as it doesn't affect the
> results).
> What happens is that all the subqueries are executed against a "before"
> image of the table. That is done because data modifications are supposed
> to be "instanteneous" - all rows are added to the table at once.
What does it do - put the whole table in memory to do that?
I assume you mean that it takes an image of the table before the first
select is done and just keeps using that?
What if I had done something like:
INSERT table2 (field1,field2,field3,field4,field5,fiel
d6,version)
SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2),
(SELECT isnull(max(version),-1)+1 FROM table3),
(SELECT isnull(max(version),-1)+1 FROM table4),
(SELECT isnull(max(version),-1)+1 FROM table5),
(SELECT isnull(max(version),-1)+1 FROM table6)
FROM table1
and I was inserting 20 records.
Would the system take a before snapshot of all 5 tables before executing the
insert/select? Would all the records have the same number in field3 of all
20 records, and the same number in field 4 of all records etc?
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Nov 2005 17:17:17 -0800, tshad wrote:
(snip)
>What does it do - put the whole table in memory to do that?
>I assume you mean that it takes an image of the table before the first
>select is done and just keeps using that?
Hi Tom,
Don't forget that my explanation is the theoretic way of handling
things. Or rather, the way it is defined in the ANSI standard. Real
databases will choose more efficient ways to achieve the same effect.
In this specific case, where there's no correlation between subquery and
main query, the optimizer will probably choose to execute the subquery
only once and remember the results.
In more compicated situation, I _think_ that the actual implementation
stores the rows to be inserted (or the new data for changed rows, in
case of an UPDATE statement) in temporary storage until the query is
completely evaluated, then moves them all at once to the table. But only
the MS developers will kno for sure how it's acutally done.
>What if I had done something like:
>INSERT table2 (field1,field2,field3,field4,field5,fiel
d6,version)
>SELECT field1,field2,(SELECT isnull(max(version),-1)+1 FROM table2),
> (SELECT isnull(max(version),-1)+1 FROM table3),
> (SELECT isnull(max(version),-1)+1 FROM table4),
> (SELECT isnull(max(version),-1)+1 FROM table5),
> (SELECT isnull(max(version),-1)+1 FROM table6)
>FROM table1
>and I was inserting 20 records.
>Would the system take a before snapshot of all 5 tables before executing th
e
>insert/select? Would all the records have the same number in field3 of all
>20 records, and the same number in field 4 of all records etc?
No - for the subqueries that read for table3 through table6, there's no
need for special handling since these tables are not affected by the
INSERT statement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
No comments:
Post a Comment