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

I got it.

INSERT table2 (field1, field2, version)
SELECT field1, field2, isnull((select max(version) from table2),-1)+1 FROM
table1

Show quoteHide quote

INSERT adds rows to a table, yet you're describing an update. So, which is it?

Please post DDL and sample data.


ML

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
Show quoteHide quote

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 confused 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
Show quoteHide quote

On Fri, 11 Nov 2005 11:18:02 -0800, tshad wrote:

(snip)
Show quoteHide quoteHi 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

Show quote Hide quote 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,field6,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

Show quoteHide quote

On Fri, 11 Nov 2005 17:17:17 -0800, tshad wrote:

(snip)
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.


Show quoteHide quoteNo - 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

On Fri, 11 Nov 2005 09:25:49 -0800, tshad wrote:

Show quoteHide quoteHi 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

Automate Importing File
concurrency in stored proc
Cursor working in this stored Procedure??
Best solution, iterate over millions records and call extended sp
convert INT