Inserting a new row into the dept table
Insert into dept (deptno, dname, loc)
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;