SELECT ... into and insert Into...select summary

SELECT... into and insert into ... Select Summary

SELECT ... INTO statement

Create a tab search.
Grammar

SELECT field1[, field2[, ...] into a new table [in external database]
From source

SELECT ... Into statements can be divided into the following sections: Partial description
Field1, field2 the name of the field that you want to copy to the new table.
NewTable the name of the table you want to create. It should follow the standard Nomenclature convention. If the name of the newtable is the same as the name of an existing table, a catch error can occur.
Externaldatabase the path into the external database. For a description of the path, see the IN clause.
The name of the existing table from which source selects records. 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

(Copy records and tables with Select)

Description

You can use a make-table query to archive records, generate copy backups of a table, or generate a copy of a table that is output to another database, or as a basis for reports that display data on a regular basis. For example, you can run the same make-table query once a month to generate a regional sales report.

Attention
Perhaps you want to define a primary key for the new table. When you create a new table, the fields in the new table inherit the data type and size of each field in the Query base table, but do not pass other fields or table properties.
Use the INSERT into statement without creating an append search to add data to the current table.
Before you run a make-table query, to know which records will be selected, you can first look at the results of a SELECT statement that uses the same selection criteria.

SELECT ... INTO statement sample

This example selects all records in the employee table and then copies them to a new table named Emp Backup.

Sub Selectintox ()


Dim DBS as Database

Dim QDF as QueryDef


' Modify this line on your computer to properly point to the path to Northwind.

Set dbs = OpenDatabase ("Northwind.mdb")

' On shipping orders exceeding $ $,


' Select All records in the employee table,

' and copy it to a new table called Employee backup.

Dbs. Execute "SELECT employees.* into" _

& "[Emp Backup] from Employees;"



' Delete QueryDef because this is a demo.

Dbs. Execute "DROP TABLE [Emp Backup];"



Dbs. Close


End Sub

Insert data into another table in Access: (Blue ideal rustling rain)

We all know select INTO bar.
Like what:
=========================
SELECT INTO TableB
SELECT * FROM TableA
where Username= ' abc '
=============================
A new table TableB is created to insert the qualifying records in the TableA into the TableB.
This is done in an MDB file.

Now I want to insert the qualifying records in the A.mdb table TableA into the TableB of B.mdb,
Do you have any good idea?
Haha, I began to think of the first to retrieve the TableA A.mdb, and then loop the recordset into B.mdb.
This requires opening two connection.

Later I found a trick (master don't say old ah ...) ）
First you have to know the physical address of the B.mdb. This is represented by a todbfile variable (which can be obtained by Server.MapPath).
====================================
Sql= "INSERT into TableB in" "& Todbfile &" ' select * from TableA where username= ' abc '
=======================================
Just establish a connection to the A.mdb. Executing this SQL statement is all OK.

INSERT INTO (select ... from ... where ... with CHECK option) values

Sql> CREATE TABLE Test (ID varchar2 () NOT NULL primary KEY,MC VARCHAR2 (60));

Table created

sql> INSERT INTO (select ID,MC from Test where id= ' 1 ') VALUES (' 1 ', ' 111111 ');

1 row inserted

Sql> select * from test;

ID MC
-------------------- ------------------------------------------------------------
1 111111

sql> INSERT INTO (select ID,MC from Test where id= ' 1 ') VALUES (' 1 ', ' 111111 ');

Insert INTO (select ID,MC from Test where id= ' 1 ') VALUES (' 1 ', ' 111111 ')

ORA-00001: Violates a unique constraint (ROME. sys_c0027236)

sql> INSERT INTO (select ID,MC from Test where id= ' 1 ') VALUES (' 2 ', ' 222222 ');

1 row inserted

sql> INSERT INTO (select ID,MC from Test where id= ') VALUES (' 3 ', ' 333333 ');

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= ' a ' with CHECK option) VALUES (' 5 ', ' 555555 ');

Insert INTO (select ID,MC from Test where id= ' under ' with CHECK option) VALUES (' 5 ', ' 555555 ')

ORA-01402: View with CHECK OPTIDN WHERE clause violation

sql> INSERT INTO (select ID,MC from test where 1=1 with CHECK option) VALUES (' 5 ', ' 555555 ');

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} Select] [Order BY column [ASC | DESC | USING operator] [, ...]] [For UPDATE [of Class_name [, ...]]] LIMIT {Count | All} [{OFFSET |,} start]
Input

All fields entered are described in detail in the Select.
Output

All fields entered are described in detail in the Select.
Describe

SELECT into creates a new table from a query. More typically, the query extracts data from an existing table, but virtually any SQL query can.

Note: The CREATE TABLE as feature is equal to the SELECT INTO command.

Common uses

Make a backup

The following example makes a backup of a "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 of the 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 creates a "Persons_backup" SELECT lastname,firstname with two columns (FirstName and LastName) by filtering the person residing in "Sandnes" from the "Persons" table. Into Persons_backup
From Persons
WHERE city= ' Sandnes '


It is also possible to select data from more than one table. The following example creates a new table, "Empl_ord_backup," that contains employees and orders two table data. 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 two table copy statements

Select * into Desttbl from SRCTBL

Insert to Desttbl (fld1, fld2) Select Fld1, 5 from Srctbl

The above two sentences are the SR The CTBL data is inserted into the DESTTBL, but the two sentences are different. The first sentence of the

(select into from) requires that the target table (DESTTBL) does not exist because it is created automatically when it is inserted. The second sentence of the

(insert into Select from) requires the target table (DESTTBL) to exist, and because the target table already exists, we can insert a constant, in addition to the field of the source table (SRCTBL), as in the example: 5. SELECT ... into and insert Into...select summary 2007-10-9 19:07:17 www.aspxuexi.com