Occasionally, you will want to add rows to a table that does not exist.
The select into statement will create a table and load the result set into the table. The syntax of the select into statement is:
Select into statement
SELECT select_list
INTO new_table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
1) The list of columns to create in the new table
SELECT select_list
INTO new_table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
2) Specifies the name of the new table
SELECT select_list
INTO new_table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
3) The rest of the select statement can be any valid select statement that does not contain the compute clause.
SQL Server select into Statement
When using a select into statement, each column in the select list must have a column header. Moreover, unlike a normal select list, each column header must only appear once in the select list.
Create an empty Table
To create an empty table that matches an existing table, use the select into statement where the WHERE clause returns no rows. Typically, the where clause would be where 1=2.
Create a new Table with "select into":
The following code sample uses the select into statement to create a new table, Persons, with three columns containing all of the rows from the Employee table.
select FName, LName, HomePhone
Into Persons
From Employee
Database Configuration:
The database must be configured to allow the select into to work. You will learn more about how to do this later in the course.
Perform a backup:
After performing a select into, you should immediately perform a full backup, since nothing is logged except for page allocation. Until you perform a full or differential backup, you can not make a usable transaction log backup. Often times you will need to load data from an operating system file into a database. SQL Server provides two ways to do this. The next lesson will cover one of the ways--the bulk insert statement.
OPENXML
Many of the concepts you have learned in this module up to this point stray toward what I would call advanced SQL Server topics.
OPENXML strays even farther, and thus I will not delve too deep into it here. I want to make sure you understand what it does and some of the situations it can be useful for. Keep in mind that many of the things OPENXML was created for are now handled in a more native way by simply placing your XML into a native XML data type and using the XML type methods discussed earlier in the module. When the original XML feature set was fi rst introduced back in SQL Server 2000, the native XML data type did not yet exist. Developers had FOR XML, and thus significant power for turning relational data into XML, but we needed something to make XML addressable in a relational formal, that something was OPENXML. OPENXML is a rowset function that opens your string much as other rowset functions (such as OPENQUERY and OPENROWSET) work. This means that you can join to an XML document, or even use it as the source of input data by using an INSERT..SELECT or a SELECT INTO.
The major difference is that it requires you to use a couple of system stored procedures to prepare your document and clear the memory after you are done using it. To set up your document, you use sp_xml_preparedocument. This moves the string into memory and pre-parses it for optimal query performance.
The XML document will stay in memory until you explicitly say to remove it or you terminate the connection that sp_xml_preparedocument was called on. The syntax is pretty simple:
sp_xml_preparedocument @hdoc = <integer variable> OUTPUT,
[, @xmltext = <character data> ]
[, @xpath_namespaces = <uri to a namespace> ]
