HELP
The help
command has been discussed above.
HELP
The help
command has been discussed above.
ADD
ADD table_name
This command, when passed a table name, prompts you for each column’s data and adds a row to the table.You may add as many rows as you wish, as the command continues until either an error occurs, or the kbd:[Enter] key is pressed without data.If you wish to set a column to NULL
, type it in exactly as shown.
SQL> add country; Enter data or NULL for each column. RETURN to end. Enter COUNTRY>Scotland Enter CURRENCY>GBP Enter COUNTRY> SQL> commit;
COPY
COPY from_table_name to_table_name [other_database]
The copy
command allows you to copy most of the structure of a table to a new table, in the current database or to a different one.Unfortunately it has a couple of problems:
It shells out to the commandline to do the work, and connects to the receiving database using an application named isql
.If, like me, your system has renamed isql
to isql-fb
, you will actually end up running the wrong isql
application and confusing error messages will be the only result.
When using an embedded connection, and the ServerMode
is set to Super
/ThreadedDedicated
(the default), a copy within the same database will fail because the database file is opened with exclusive access by the first isql
session.
It assumes that isql
will be on the $PATH
or %PATH%
.
You need to define ISC_USER
and ISC_PASSWORD
for the child isql
process to log in to the receiving database to create the table.This is very insecure.
Because of the need for ISC_USER
and ISC_PASSWORD
, the receiving database must be running on the same server as the source database.
The data in the table is not copied to the receiving database.Only the following parts of the table’s structure is copied.
Domains required to recreate the table.This only applies if the copy is to another database.
The table itself will be created.
Primary key constraint, if there is one.
The index used to support the primary key constraint, if there is one.
Not all structure of the table is actually copied.Missing are:
Foreign Key constraints.
Check constraints.
Indices other than the primary key index.
Triggers.
All table data.
If you wish to copy to a different database, then the other database must be on the same server as the current one.You cannot, for example, connect to a database on a server named tux
, and copy a table to a database running on the server tuxrep
.The copy
command has no way to allow you to pass a username and/or password and, equally, setting ISC_USER
and ISC_PASSWORD
only affects databases on the current server.
tux> $ export ISC_USER=SYSDBA tux> $ export ISC_PASSWORD=secret tux> isql employee Database: employee, User: sysdba SQL> -- MAke a copy of the employee table into this database. SQL> copy employee employee_2; SQL> -- Compare table structures... SQL> show table employee; EMP_NO (EMPNO) SMALLINT Not Null FIRST_NAME (FIRSTNAME) VARCHAR(15) Not Null LAST_NAME (LASTNAME) VARCHAR(20) Not Null PHONE_EXT VARCHAR(4) Nullable HIRE_DATE TIMESTAMP Not Null DEFAULT 'NOW' DEPT_NO (DEPTNO) CHAR(3) Not Null CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL) JOB_CODE (JOBCODE) VARCHAR(5) Not Null CHECK (VALUE > '99999') JOB_GRADE (JOBGRADE) SMALLINT Not Null CHECK (VALUE BETWEEN 0 AND 6) JOB_COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null SALARY (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0 CHECK (VALUE > 0) FULL_NAME Computed by: (last_name || ', ' || first_name) CONSTRAINT INTEG_28: Foreign key (DEPT_NO) References DEPARTMENT (DEPT_NO) CONSTRAINT INTEG_29: Foreign key (JOB_CODE, JOB_GRADE, JOB_COUNTRY) References JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY) CONSTRAINT INTEG_27: Primary key (EMP_NO) CONSTRAINT INTEG_30: CHECK ( salary >= (SELECT min_salary FROM job WHERE job.job_code = employee.job_code AND job.job_grade = employee.job_grade AND job.job_country = employee.job_country) AND salary <= (SELECT max_salary FROM job WHERE job.job_code = employee.job_code AND job.job_grade = employee.job_grade AND job.job_country = employee.job_country)) Triggers on Table EMPLOYEE: SET_EMP_NO, Sequence: 0, Type: BEFORE INSERT, Active SAVE_SALARY_CHANGE, Sequence: 0, Type: AFTER UPDATE, Active SQL> show table employee_2; EMP_NO (EMPNO) SMALLINT Not Null FIRST_NAME (FIRSTNAME) VARCHAR(15) Not Null LAST_NAME (LASTNAME) VARCHAR(20) Not Null PHONE_EXT VARCHAR(4) Nullable HIRE_DATE TIMESTAMP Not Null DEFAULT 'NOW' DEPT_NO (DEPTNO) CHAR(3) Not Null CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL) JOB_CODE (JOBCODE) VARCHAR(5) Not Null CHECK (VALUE > '99999') JOB_GRADE (JOBGRADE) SMALLINT Not Null CHECK (VALUE BETWEEN 0 AND 6) JOB_COUNTRY (COUNTRYNAME) VARCHAR(15) Not Null SALARY (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0 CHECK (VALUE > 0) FULL_NAME Computed by: (last_name || ', ' || first_name) CONSTRAINT INTEG_93: Primary key (EMP_NO) SQL> -- Check indices on both tables... SQL> show indices employee; NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME) RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO) RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY) RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO) SQL> show indices employee_2; RDB$PRIMARY27 UNIQUE INDEX ON EMPLOYEE_2(EMP_NO) SQL> -- Check data counts on both tables... SQL> select count(*) from employee; COUNT ============ 42 SQL> select count(*) from employee_2; COUNT ============ 0
The copy
command only works provided your isql
application is really named isql
.In addition, if you have lots of data in the table, you still have to copy it manually as the copy
command will only copy the table structure.Remember that the new table will have no triggers, no foreign keys, no indices — other than the primary key one — and no data.
Note
|
It is possible that the |
INput
INput filename
This command enables the user to execute a number of commands from a script file rather than manually typing them all into isql
at the prompt.The script may contain any mix of DDL and/or DDL commands, along with isql
commands to redirect output, change options, etc.
SQL> shell; $ cat test.sql drop table fred; commit; $ exit; SQL> show table fred; A INTEGER Nullable B INTEGER Not Null SQL> input test.sql; SQL> show table fred; There is no table FRED in this database