SQL Insert

Inserting a new row into the dept table

Insert into dept (deptno, dname, loc)

values (10,'Apple','Boston')

You can omit the column list in the insert statement but then you have to enter those in the same order as they appear in the table and you have o include all the columns in the values.

The following example illustrates the use of the "default" keyword while inserting the records.

insert into table_name values(default);

or

insert into table_name(column1,column2..) values(default) 

How to create a new table having the same structure as some other table?

create table new_table

as

select *

from old_table

How to insert data into multiple tables?

There are two terms used INSERT ALL AND INSERT FIRST

EXAMPLE:
Suppose there are three tables emp, emp_1 and emp_2 with the same structure and columns.

insert all

when sal in (800, 1600) then

into emp (empno, ename,job) values (empno, ename,job)

when sal = 3200 then

into emp_1 (empno, ename, job) values (empno,ename,job)

else

into emp_2 (empno, ename,job) values (empno,ename,job)

select empno, ename, job

from emp

INSERT FIRST will breakout as soon as it sees a condition that evaluates to true.

INSERT ALL will evaluate al the conditions even if the previous condition evluates to true.

Insert Default Values

Some RDBMSs, including SQL Server, allow you to create a new row based on default values that are defined in the table specification using the DEFAULT VALUES method in place of the VALUES section. Suppose you have a table like this:

CREATE TABLE A (

A_ID int IDENTITY PRIMARY KEY,

Name varchar (100) DEFAULT 'Anonymous',

Dates varchar (20) DEFAULT 'Unknown);

And you if want to insert a new row with just the default values for each column and the auto-generated ID field. You can’t do it using the standard INSERT...VALUES syntax, so instead you'd use this:

INSERT INTO A DEFAULT VALUES;

And that does the trick. Using DEFAULT VALUES instead of the standard VALUES clause inserts the default value for every column (or an auto-generated value for identity columns). If there isn't a default value for a non-identity column, the value is treated as NULL. If any column in the table is NOT NULL, isn't an identity column, and doesn't have a default value, then an error will be raised.

Performing a Multi-Row INSERT

INSERT INTO S_Backup SELECT EveryName, EveryCost FROM S;

Alternatively, you can easily reshape data and rename columns of rows using a statement such as this:

   
INSERT INTO S_1 (TheName, TheCost)

SELECT EveryName, EveryCost FROM S;