auto increment on temp.table with select ...

[Hylsan]

Hi!
Got a stored procedure that looks something like this;

CREATE TEMPORARY TABLE tmp_skatt (transnr INTEGER, ewc VARCHAR(50), vikt DOUBLE);

       /* Plockar ut "allt in" */
       insert into tmp_skatt
       select trans2009.transnr, 'Allt in', sum(trans2009.nettovikt) vikt from trans2009 inner join artikel on trans2009.artikel = artikel.artikel where
       trans2009.transtid between dat1 and dat2 and trans2009.kund='101' and trans2009.status='2' and trans2009.artikel between '1000' and '9999'
       and artikel.ewc like '1-%-%'
       group by artikel.sandstatus;

       /* Plockar ut "uppstått inom anläggning" */
       insert into tmp_skatt
       select trans2009.transnr, 'Uppstått inom anläggningen', sum(trans2009.nettovikt) vikt from trans2009 inner join artikel on trans2009.artikel = artikel.artikel where
       trans2009.transtid between dat1 and dat2 and trans2009.kund='101' and trans2009.status='2' and trans2009.artikel between '1000' and '9999'
       and artikel.ewc like "2-%-%"
       group by artikel.sandstatus;

       select * from tmp_skatt;

      /* Rensar ut i tmp-tabellerna  */
      DROP TEMPORARY TABLE tmp_skatt;

I would like to change "transnr" in CREATE TEMP... to a "id INTEGER AUTO_INCREMENT" and then adding a PRIMARY KEY(ID).

The problem is;
How do I write the INSERT INTO...??

Thanks in advance!
/Hylsan

 

[DonQuichote]

Just like you wrote it:

CREATE TEMPORARY TABLE tmp_skatt
      (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
       ewc VARCHAR(50),
       vikt DOUBLE );
[code]
I do not know which engine you use for your temporary table though. Not all engines support auto-increments, but most do. If you use InnoDB, MyISAM or even MEMORY, it should be no problem.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
   Hex (in Darwin's Watch)

 

[Hylsan]

but how do I write the line;

insert into tmp_skatt
       select trans2009.transnr, 'Allt in', sum(trans2009.nettovikt) vikt from trans2009 .....

Its the "trans2009.transnr" I would like to change so that it is using the "id" instead.

/Hylsan

 

[DonQuichote]

Give the columns that you want to insert to in the INSERT statement:

INSERT INTO tmp_skatt(ewc,vikt) SELECT 'Allt in', sum(trans2009.nettovikt) FROM ...

Note that you do not give the id column. If you do want to give the id column (or omit the fields entirely as you gave done and as the SQL standard says) set it to NULL.

inserting NULL into an auto-increment column will automatically cause it to generate the next value.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[Hylsan]

I tried that I believe. Gonna give it a go again...thanks!

/Hylsan

 

[Hylsan]

Doing as you wrote, I ended up with a '0' as id.
Tried setting it to NULL, but didnt succed.
Might be my fault since I dont know how to.

wrong?

insert into tmp_skatt(id,ewc,vikt)
       select NULL, 'Allt in', ifnull(sum(trans2009.nettovikt),0) v....

says that 'id' cannot be 'NULL'.

/Hylsan