Select into and insert

 

Select... into statement

Create tabulation search.
Syntax

Select field1 [, field2 [,...] into new table [in external database]
From Source

The Select... into statement can be divided into the following parts:
The name of the field to be copied to the new table.
The name of the table to be created in newtable. It should comply with the standard naming convention. If the newtable name is the same as the existing table name, an error can be caught.
The path from externaldatabase to external database. For more information about paths, see the in clause.
Source selects the name of the existing table from the record. It can be a single table or multiple tables or a query.
View:

Http://www.aspxuexi.com/ SQL/SQL /2006-4-8/select_insert.htm

(Use select to copy records and tables)

Description

You can use generate table queries to archive records, generate copies and backups of tables, generate copies of tables output to another database, or use them as the basis for regular data display reports. For example, you can run the same generated Table query once a month to generate a sales monthly report for the region.

Note:
You may want to define a primary key for the new table. When creating a new table, the fields in the new table inherit the data type and size of each field in the basic table, but other fields or table attributes are not passed.
Use the insert into statement instead of creating an append query to add data to the current table.
Before running the generated Table query, to know which records will be selected, You can first take a look at the results of the SELECT statement using the same selection conditions.

Example of select... into statement

In this example, all records in the employee table are selected and copied to the new table named EMP backup.

Sub selectintox ()

Dim dbs as database

Dim qdf as querydef

'Modify this exercise on your computer and correct it to the northwind path.

Set DBS = opendatabase ("northwind. mdb ")

'For orders with freight exceeding $100,

'Select all records in the employee table,

And copy to a new table called employee backup.

DBS. Execute "select employees. * "_

& "[EMP backup] from employees ;"

 

'Delete querydef because this is a demonstration.

DBS. Execute "Drop table [EMP backup];"

 

DBS. Close

End sub

Insert data to another table in access: (Blue is ideal, Xiao Xiaoyu)

Everyone knows select.
For example:
======================================
Select into tableb
Select * From tablea
Where username = 'abc'
==================================
A new table named tableb is created, and the qualified records in tablea are inserted into tableb.
This operation is performed in an MDB file.

Now I want to insert the qualified records in Table A of table A in table B of Table B. MDB,
Do you have any good solutions?
Haha, I first thought about retrieving a. mdb of tablea, and then inserting the cyclic record set into B. MDB.
You need to enable two connections.

Later, I found a trick (master should not say old ......)
First, you must know the physical address of B. MDB. The todbfile variable is used here (it can be obtained using server. mappath)
==========================================
SQL = "insert into tableb in '" & todbfile & "'select * From tablea where username = 'abc '"
========================================================
You only need to establish a connection to a. MDB. Execute this SQL statement.

 

--------------------------------------------------------------------------------

 

Insert into (Select... from... where... with check option) Values

SQL> Create Table Test (ID varchar2 (20) not null primary key, MC varchar2 (60 ));

Table created

SQL> insert into (select ID, MC from test where id = '1') values ('1', '123 ');

1 row inserted

SQL> select * from test;

Id MC
--------------------------------------------------------------------------------
1 111111

SQL> insert into (select ID, MC from test where id = '1') values ('1', '123 ');

Insert into (select ID, MC from test where id = '1') values ('1', '123 ')

ORA-00001: violation of unique constraints (Rome. sys_c0027236)

SQL> insert into (select ID, MC from test where id = '1') values ('2', '123 ');

1 row inserted

SQL> insert into (select ID, MC from test where id = '10') values ('3', '123 ');

1 row inserted

SQL> select * from test;

Id MC
--------------------------------------------------------------------------------
1 111111
2 222222
3 333333

SQL> insert into (select ID, MC from test where id = '10' with check option) values ('5', '123 ');

Insert into (select ID, MC from test where id = '10' with check option) values ('5', '123 ')

ORA-01402: view with check optidn WHERE clause violations

SQL> insert into (select ID, MC from test where 1 = 1 with check option) values ('5', '123 ');

1 row inserted

SQL> select * from test;

Id MC
--------------------------------------------------------------------------------
1 111111
2 222222
3 333333
5 555555

 

--------------------------------------------------------------------------------

 

PostgreSQL

Name

Select into-create a new table from the current table or view
Select [All | distinct [ON (expression [,...])] expression [as name] [,...] [into [temporary | temp] [Table] new_table] [from table [alias] [,...] [Where condition] [group by column [,...] [Having condition [,...] [{Union [all] | intersect | except t} select] [order by column [ASC | DESC | using operator] [,...] [For update [of class_name [,...] limit {count | all} [{offset |,} start]
Input

All input fields are described in detail in select.
Output

All input fields are described in detail in select.
Description

Select into creates a new table from a query. Typically, this query extracts data from an existing table, but in fact any SQL query can.

Note: the create table as function is equivalent to the select into command.

 

--------------------------------------------------------------------------------

 

Common usage

Create backup

The following example creates a backup for the "persons" table. Select * into persons_backup
From persons

The in clause can be used to copy a table to another database. Select persons. * into persons in 'backup. mdb'
From persons

If you only want to copy some fields, you can also list the fields after the SELECT statement. Select lastname, firstname into persons_backup
From persons

You can also add a clause. The following example uses the "persons" table to filter people living in "sandnes" and uses two columns (firstname and lastname) to create a "persons_backup" select lastname, firstname into persons_backup
From persons
Where city = 'sandes'

It is also possible to select data from more than one table. The following example creates a new table "empl_ord_backup" that contains the data of employees and orders tables ". Select employees. Name, orders. Product
Into empl_ord_backup
From employees
Inner join orders
On employees. employee_id = orders. employee_id

 

--------------------------------------------------------------------------------

 

The difference between the select into and insert into select table copy statements

Select * into desttbl from srctbl

Insert into desttbl (fld1, fld2) Select fld1, 5 from srctbl

The preceding two statements Insert the srctbl data to desttbl, but the two statements are different.

The first clause (select into from) requires that the target table (desttbl) does not exist because it is automatically created during insertion.

The second sentence (insert into select from) requires that the target table (desttbl) exists. Because the target table already exists, We can insert constants in addition to the fields in the source table (srctbl, in this example: 5.