| Lesson 3 | The select into statement |
| Objective | Load data with the select into statement. |
Select into Statement using SQL-Server
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
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:
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> ]
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
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
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.