CODEWITHSUNDEEP
sql
Stefan
Stefan

Reputation: 3869

Inserting into table from another table with an extra variable

I have two tables

Table A 
(
unique id, -- sum of the records
Data1,
Data2
)

Table B
(
Data1,
Data2
)

I need to insert from table B into table A and add the records in. I had a simple insert like this:

Insert into A select * from B

but that does not work due to the unique ID.

I tried something like:

INSERT INTO A
SELECT 1+SELECT MAX(UniqueID) FROM A,temp.* 
FROM B temp;

But this does not work. Can anyone think of a way I can get the first parameter to be a count of the records, obviously it would need to increase by one each time a new record is added.

Upvotes: 2

Views: 3963

Answers (2)

Hadmacker
Hadmacker

Reputation: 36

In past Oracle DB's, I've used a sequence to create a primary key ID for Table A, so you should be able to do this:

CREATE SEQUENCE A_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;    

insert into A (id, Data1, Data2)  select A_SEQ.nextval, Data1, Data2 from B

See this url for details: http://www.techonthenet.com/oracle/sequences.php

** In SQL Server, you can use the "autoincrement" attribute on a column to do the same. ** SQL Server 2012 will also be gaining a similar Sequence mechanism, though its syntax is quite different than this.

** Edit: My preference is still to use sequences, but as an alternative, this re-work of your original SELECT statement might be helpful:

INSERT INTO A (UniqueID, Data1, Data2) 
SELECT 
    (SELECT MAX(UniqueID) + 1 FROM A)
  , Data1
  , Data2 
FROM B

Upvotes: 1

Matt Fenwick
Matt Fenwick

Reputation: 49115

Just set id to be an auto-increment column in the table declaration:

create table A (
  id int primary key auto_increment,
  Data1 <sometype>,
  Data2 <sometype>
);

Then you can insert rows from B:

insert into A (Data1, Data2) 
select Data1, Data2 from B

Each new row in A will get a new, unique auto-increment value for id.

Note: not experienced with plsql, but this is what I'd do with MySQL.

Upvotes: 1

Related Questions