SQL populating tables in oracle.?

Question

i have written an sql script to create a table as follows:

CREATE TABLE COMPANY
(
COMPANYID NUMBER NOT NULL PRIMARY KEY,
COMPANY_NAME VARCHAR(60) UNIQUE,
COMPANY_SIZE CHAR(11)
);

and im trying to write another sql script to populate it. so far i have been using varients of:

INSERT INTO COMPANY (COMPANYID, COMPANY_NAME, COMPANY_SIZE)
SELECT 1,TEXT,1

however i cant seem to update the table. Eventually i want to be able to add a few lines of random data using an auto number for the companyid

i am using oracle sql developer

Thanks!!

Answer 1

Hi



Oracle does not have a autonumbering the same as sqlserver or informix etc, so you need to create something called a sequence:



First, run this:



CREATE SEQUENCE company_seq

minvalue 1

start with 1

increment by 1

cache 20;



This creates a 'sequence', and internal counter if you like.



Now, whenever you INSERT a record:



INSERT INTO COMPANY (COMPANYID, COMPANY_NAME, COMPANY_SIZE)

SELECT company_seq.nextval,TEXT,1



Try that.



J.

Answer 2

Autonumber should automatically incrememt itself. The recommended SQL would be like this:



CREATE TABLE `COMPANY`

(

COMPANYID NUMBER NOT NULL PRIMARY KEY,

COMPANY_NAME VARCHAR(60) UNIQUE,

COMPANY_SIZE CHAR(11)

);



*NOTE: THE FOLLOWING IS AN EXAMPLE!

INSERT INTO COMPANY(1, Top Computer Services, 500);



SELECT (Column names) FROM (table) WHERE (condition);



For example, the SELECT command may be like this:

SELECT * FROM `COMPANY` WHERE `COMPANYID` == 1;

This will extract from the database, every column where the COMPANYID is equal to 1.



Hope this makes sense?

shanet

Answer 3

Neneg's answer is fine if you defined the newmusic table's columns in the corresponding order as myspacemusic (the columns names do not have to match between the tables btw)...if not, you'll have to specify the myspacemusic columns in the appropriate order to ensure the columns from the two tables map correctly to each other.

Answer 4

INSERT INTO COMPANY (COMPANYID, COMPANY_NAME, COMPANY_SIZE)

SELECT 1,'TEXT',1

union all

SELECT 2,'TEXT2',1

union all

SELECT 3,'TEXT3',1



COMPANYID and COMPANY_NAME values should be unique over the table according to your schema.