TechTutorials - Free Computer Tutorials  







SQL 7.0 Data Loading 
 


Added: 04/13/2001, Hits: 2,738, Rating: 0, Comments: 0, Votes: 0
Add To Favorites | Comment on this article
By Brian Talbert

The Microsoft Skill Matrix lists as one of the exam objectives, methods of loading data. Examples are listed as the INSERT statement, the SELECT INTO statement, the bcp utility, Data Transformation Services (DTS), and the BULK INSERT statement.

INSERT
You should already be familiar with the INSERT statement. It is a T-SQL command that allows you to, appropriately, insert new rows of data into a table. This command is only used to APPEND data. It does not update existing rows, nor would it delete existing rows. If the goal is to replace the data in a table, you would need to first delete the table before populating with the INSERT command. A simple example syntax is as follows:

Code :

INSERT MY_TABLE VALUES ('COLUMN_1_DATA', 'COLUMN_2_DATA')


This basic command assumes a table name of "my_table" and it also assumes that it has two columns, each support the type of data entered. It also assumes that the values being inserted are in the same order as the columns within the tables. If the values were not being entered in precise order it would be necessary to specify to which columns the values should be applied. For instance:

Code :

INSERT MY_TABLE ("Column1", "Column2") VALUES ('COLUMN_1_DATA', 'COLUMN_2_DATA')


You should also note that all columns must be specified, unless the columns generate data automatically (such as would be the case with an identity column), or the columns allow nulls.

BULK INSERT
The BULK INSERT command is used with T-SQL batches to move data from a file into a table or view. It is the fasted method of quickly inserting large amounts of data, provided the source text file also resides on the server. The basic command syntax would following something like this:



Code :

BULK INSERT MyTable FROM 'c:mydatamyfile.txt'
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = 'n')


Refer to Books Online for the complete syntax. There are quite a few options for using this command. The simple example here shows data being inserted to MyTable from a file on the C: drive. The file is a comma delimited file, with ends of rows being indicated by a new line (n).

SELECT INTO
The INTO clause of the select statement allows you to create a brand new table and populate it with the results of the query being performed by the SELECT statement.

Data Transformation Services (DTS)
DTS is an extremely powerful new feature of SQL Server 7.0. DTS allows you to transfer data from multiple heterogeneous sources to multiple heterogeneous destinations. You can manipulate the data while being transformed, even using scripting languages such as VBscript or Jscript. It can transfer data, or even objects between MS SQL Servers. DTS can serve as the data pump to build your data warehouse. The capabilities of DTS are incredible and simply too much to detail here. While DTS has not been reported to be covered in much depth on the exam, you should familiarize yourself with its many powerful features.

Bulk Copy Program (BCP)
BCP is a command line program that either exports data from SQL Server to a text file or imports data to SQL Server from a text file. It is important to understand BCP and the basic command syntax. Make sure your read up on BCP in Books Online.





Comments (0)

Be the first to comment on this article


Related Items








7 Seconds Resources, Inc.




IT Showcase