1 / 14

SQL Server 2005 Ch10

SQL Server 2005 Ch10. Working with Flat Files. Working with Flat Files. Flat File – non hierarchical file that contains data In a flat file, data is usually delimited by some character such as “ , “ (comma) Records are usually delimited by {return} or new line

yestin
Download Presentation

SQL Server 2005 Ch10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 Ch10 Working with Flat Files

  2. Working with Flat Files • Flat File – non hierarchical file that contains data • In a flat file, data is usually delimited by some character such as “ , “ (comma) • Records are usually delimited by {return} or new line • Some flat files specify columns by column positioning i.e. first field is between column 1 and column 25 • SQL Server supplies many tools to allow for importing and exporting flat files to and from the database • Bcp – buld copy program • BULK INSERT T-SQL command • OPENROWSET T-SQL command • SSIS Import/Export Wizard

  3. Preparing to Work with Flat Files • Utilize Bulk-Logged recovery model so that transactions are not logged • ALTER DATABASE [databaseName] SET RECOVERY BULK_LOGGED; • If There is an index on table and table has data already, Logging level will not be Minimal, therefore if you want to utilize minimal logging, you must disable the indexes first, load data, then rebuild the indexes • Must also answer the following questions: • Source File Location • Import Mechanism • Data Destination

  4. Running the bcp Utility • Oldest of the SQL Server utilities for importing and exporting data • Out-of-Process utility (ie is external to the SQL Server service) that is run from the command line • Can import or export data • 2 limitations • Limited data-transformation capabilities • Limited error handling capabilities • Important parameters • -t column delimiter default is \t(tab) • -r row delimiter default is \n(new line) • -F defines first row to import default is line 1 • -h hint parameter • TABLOCK – locks table • ORDER specifies that records in the data file are ordered by certain columns

  5. BCP Cont. • Minimum security permissions needed to execute are SELECT/INSERT permissions • To utilize suspend trigger execution, suspend constraint checking, or to use keepidentity option, the user must have ALTER TABLE permissions

  6. Running BCP Cont. • Syntax • bcp {[[database_name.][owner].]{table_name | view_name} | "query"} • {in | out | queryout | format} data_file • [-mmax_errors] [-fformat_file] [-x] [-eerr_file] • [-Ffirst_row] [-Llast_row] [-bbatch_size] • [-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6] • [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] • [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size] • [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword] • [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"] • Examples: • bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c • bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c

  7. Performing a Bulk Insert Task • In-process (i.e. run from SQL Server Service) way of bulk inserting information • Cannot be used for export • Important parameters • FieldTerminator – default /t(tab) • RowTerminator – default /n(new line) • FirstRow Default 1

  8. Performing a Bulk Insert Task Cont. • Permissions needed • If utilizing Windows security, user must have read access to the import file that is outside of SQL Server. • If in mixed mode and user is not a windows user, the user utilized the SQL Server Service account rights for file access • User must also have INSERT and ADMINISTER BULK OPERATION • User must have ALTER TABLE permissions to suspend constraint checking, or to use KEEPIDENTITY option

  9. Performing a Bulk Insert Task Cont. • Syntax • BULK INSERT • [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] • FROM 'data_file' • [ WITH • ( • [ [ , ] BATCHSIZE = batch_size ] • [ [ , ] CHECK_CONSTRAINTS ] • [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] • [ [ , ] DATAFILETYPE = • { 'char' | 'native'| 'widechar' | 'widenative' } ] • [ [ , ] FIELDTERMINATOR = 'field_terminator' ] • [ [ , ] FIRSTROW = first_row ] • [ [ , ] FIRE_TRIGGERS ] • [ [ , ] FORMATFILE = 'format_file_path' ] • [ [ , ] KEEPIDENTITY ] • [ [ , ] KEEPNULLS ] • [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] • [ [ , ] LASTROW = last_row ] • [ [ , ] MAXERRORS = max_errors ] • [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] • [ [ , ] ROWS_PER_BATCH = rows_per_batch ] • [ [ , ] ROWTERMINATOR = 'row_terminator' ] • [ [ , ] TABLOCK ] • [ [ , ] ERRORFILE = 'file_name' ] • )]

  10. Performing a Bulk Insert Task Cont. • BULK INSERT AdventureWorks.Sales.SalesOrderDetail • FROM 'f:\orders\lineitem.tbl' • WITH • ( • FIELDTERMINATOR =' |', • ROWTERMINATOR =' |\n' • )

  11. Utilizing OPENROWSET • OPENROWSET can be used to open almost any ODBC, OLE-DB source including excel, access, xml, text, or another database. • Can be the target of an Insert, Update, or Delete query

  12. Utilizing OPENROWSET Cont • Syntax: • OPENROWSET • ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' • | 'provider_string' } • , { [ catalog. ] [ schema. ] object • | 'query' • } • | BULK 'data_file' , • { FORMATFILE = 'format_file_path' [ <bulk_options> ] • | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } • } ) • <bulk_options> ::= • [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] • [ , ERRORFILE = 'file_name' ] • [ , FIRSTROW = first_row ] • [ , LASTROW = last_row ] • [ , MAXERRORS = maximum_errors ] • [ , ROWS_PER_BATCH = rows_per_batch ]

  13. Utilizing OPENROWSET Cont • Examples: • SELECT CustomerID, CompanyName • FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', • 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; • 'admin';'',Customers) • GO • INSERT INTO myTable(FileName, FileType, Document) • SELECT 'Text1.txt' AS FileName, • '.txt' AS FileType, • * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document

  14. Using the SSIS Import/Export Wizard • To start the Wizard from SSIS, right click any database container in the tree, select Tasks, then choose either Import or Export Data • First you must choose your datasource i.e. another database, flat file, xml file, access, excel, etc. Based on your datasource choice, you will be directed to point to either the database or file where the datasource exist • You next choose your destination. Again can be any of the above datasource types • Select source tables and views. Depending on source data, can be selecting tables, views, or other objects, or specifying rows, etc from a file. • Save and Execute package – you can either save the package for later execution or execute immediately

More Related