Skip to main content

SQL SELECT INTO Statement

The SQL SELECT INTO statement can be used to create backup copies of tables.

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table, creates a new table with the exact structure and size and inserts automatically the selected data into the new table.

SQL SELECT INTO Syntax

We can select all columns into the new table:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
Or we can select only the columns we want into the new table:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
We use the following SQL statement:
SELECT *
INTO Persons_Backup
FROM Persons
We can also use the IN clause to copy the table into another database:
SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
We can also copy only a few fields into the new table:
SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.
The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":
SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
Labels:

Comments

Post a Comment

Popular posts from this blog

What is .csp extension? C++ Server Pages

C++ Server Pages C++ Server Pages (CSP) is a Web Engine for advanced Web Application Development, that uses blended Markup Language / C++ scripts ( such as HTML/C++, XML/C++, WML/C++ etc.) Similar to ASP and JSP, it provides a great easiness in creating web pages with dynamic content, as well as complex business applications. However, instead of Java, Javascript or VBscript, it uses C++ . This brings some significant advantages: Incredibly high processing efficiency. Benchmarks have shown a range of 80 to 250 times higher processing speed than ASP. The use of pure C++ allows the use of tons of libraries that are currently available. It is important to notice that the libraries written in C++ are tens or hundreds of times more than in any other language. It is widely accepted that the most skilled programmers in the IT market are the C++ ones. However, CGI, ISAPI and other frameworks where C++ applies, do not provide the web developer with facilities for efficient app...
Post a Comment
Read more

Valid styles for converting datetime to string

I wrote this little table and procedure to help me remember what style 104 did, or how to get HH:MM AM/PM out of a DATETIME column. Basically, it populates a table with the valid style numbers, then loops through those, and produces the result (and the syntax for producing that result) for each style, given the current date and time. It uses also a cursor. This is designed to be a helper function, not something you would use as part of a production environment, so I don't think the performance implications should be a big concern. Read more »
Post a Comment
Read more