Pakistan lifts block on Twitter - ZDNet Asia: Pakistan lifts block on TwitterZDNet Asia59 Jakarta 10350, Indones... http://t.co/61n85ajh
21 minutes ago by semarang_photo on twitter
Breaking News: Yahoo announces Alibaba stake sale
ZDNet / Techguide / Database
Bulk importing data into SQL Server
Summary
If you work with databases, at some point, you’ll deal with inserting data into SQL Server from outside data files.
Events
IBM Technology Conference & Expo 2012
May 22, 2012
One World Hotel, First Avenue, Bandar Utama City Centre, 47800 Petaling Jaya, Selangor
Echelon 2012
June 11 and 12, 2012
University Cultural Centre, National University of Singapore
Startup Asia Jakarta 2012
June 7 and 8, 2012
12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia
MMA Forum Singapore
April 23-25, 2012
Grand Hyatt Singapore
This tutorial shows how to import data using the BULK INSERT command and explains how changing some of its options can make it easier and more efficient to insert data.
Bulk Insert
BULK INSERT is a TSQL command used in SQL Server to load an external file into a database table using a specified format. This gives the developer the ability to import directly into database tables without using an outside program, such as Integration Services.
Although BULK INSERT doesn't allow any complex logic or transformations, it does offer useful options in terms of formatting the data and how the import is accomplished.
One limitation to BULK INSERT is that it only puts data into SQL Server.
Inserting data
The following example will give you a better idea of how to use the BULK INSERT command.
First, I'll create the SalesHistory table; this is where I insert data from the text file.
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int],
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
When BULK INSERT is used to insert data into a database table, triggers on that table will not be fired by default. This is convenient because triggers slow down the import process.
In the next example, I create a trigger on the SalesHistory table that prints out the number of records inserted into the table.
CREATE TRIGGER tr_SalesHistory
ON SalesHistory
FOR INSERT
AS
BEGIN
PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.'
END
The file I use to insert data is a text file with the values separated by commas. This file has 1,000 records in it, and the fields relate directly to the fields in the SalesHistory table I just created.
This script uses BULK INSERT to insert the data in the SalesHistoryText table into the SalesHistory table. Because the values in the text file are comma seperated, all I need to specify for the data to be inserted is the FIELDTERMINATOR.
Notice that the trigger I just created is not fired when this statement is run:
BULK INSERT SalesHistory FROM 'c:SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')
Sometimes it is necessary to fire triggers when importing a large chunk of data. The following script uses the FIRE_TRIGGERS option to indicate that any triggers on the destination table should be fired:
BULK INSERT SalesHistory FROM 'c:SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS)
You can use the BATCHSIZE command to set how many records will be inserted into the table as a single transaction.
In the previous example, all 1,000 records were inserted into the table in the same transaction. In this example, I set the BATCHSIZE to 2, which means that 500 individual transactions will be committed on the table. This also means that the trigger will fire 500 times, so 500 prints statements will be output to the screen.
BULK INSERT SalesHistory FROM 'c:SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',', FIRE_TRIGGERS, BATCHSIZE = 2)
BULK INSERT is not limited to locally mapped drives in SQL Server 2005. The following statement shows how you can import data from the SalesHistoryText file from the D drive on a server named FileServer.
BULK INSERT SalesHistory FROM 'FileServerD$SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')
Sometimes it is nice to view the data you are importing before committing it to a database table.
The following statement uses the OPENROWSET function, along with the BULK command, to read data from the SalesHistoryText text file. This statement also uses a format file that indicates how the data in the text file is formatted.
SELECT *
FROM OPENROWSET(BULK 'c:SalesHistoryText.txt' ,
FORMATFILE='C:SalesHistoryFormat.Xml'
) AS mytable;
GO
Tim Chapman, an SQL Server database administrator and consultant working for a bank, has more than eight years of IT experience, and is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail chapman.tim@gmail.com.
Talkback
Multiple Files...
Did you get an answer to your multiple files question. I'm wondering the same thing.
Thanks!
RE: Can I specify Multiple Files?
That's the exact question i am here for, I am new to this but one xml file does not seem like a BULK insert, I have over 1,000 xml invoices to insert, can you specify the whole folder?
Resource Centre Useful content from our premier sponsors
Database TechGuides
Audit data using SQL Server 2005's COLUMNS_UPDATED function
Physically destroy your hard disk for security
Simplify queries with a Dates table
Document SOX compliance with SQL Server 2005
Diagnose performance issues via sp_lock
Build a file location on the fly
Generate dynamic SQL statements in SQL Server
Write a common sorting algorithm
Develop an SQL Server Job from scratch
Correct data casing in SQL Server
ZDNet Asia Live
Pakistan lifts block on Twitter http://t.co/WHqoJOqm http://t.co/erFX4aVv #arcavir
21 minutes ago by V_RaV on twitter
http://t.co/VNaZtseV Pakistan lifts block on Twitter: Country restores access after briefly ... http://t.co/5gqegFWK http://t.co/wiqY9ktt
21 minutes ago by RavtachSolution on twitter
Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn
35 minutes ago by zdnetasia on twitter
Mac users' indifference toward security 'worrying' - ZDNet Asia: Mac users' indifference toward security 'worryi... http://t.co/CD9pvW08
35 minutes ago by win7antivirus on twitter
RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn
35 minutes ago by t_phuck on twitter
Mac users' indifference toward security 'worrying' - ZDNet Asia: USA TODAYMac users' indifference toward securit... http://t.co/4EUVidiO
36 minutes ago by Namosofts on twitter
Mac consumers indifferent about security, security vendors warn such mindset is "worrying" http://t.co/ZGIxdg67 #In
36 minutes ago by EllyZDNetAsia on twitter
Mac users take note! RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/YrLB9btb #mac #apple
36 minutes ago by jolintan on twitter
RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/i7gZ8WVn
36 minutes ago by KevinZDNetAsia on twitter
Mac users' indifference toward security 'worrying': However, Mac users ZDNet Asia spoke to expressed indifferenc... http://t.co/15DulmWS
36 minutes ago by ArkinOttman54 on twitter
RT @jolintan: Mac users take note! RT @zdnetasia: Mac users' indifference toward security 'worrying'. http://t.co/2RQkfCKt #mac #apple
36 minutes ago by ewenboey on twitter
APAC tech merger and acquisition in Q1 down but value up: http://t.co/V7UkMABl
36 minutes ago by CopenhagenINK on twitter
Mac users' indifference toward security 'worrying' - ZDNet Asia: Mac users' indifference toward security 'worryi... http://t.co/PINqvJxT
50 minutes ago by antivirusdown on twitterI reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...
2 hours ago by wykoong on Gartner: Mobile CRM gives better ROI than socialThis video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...
19 hours ago by TradeBrother on A quick fill handle trick for Microsoft Excelwaiting...
2 days ago by eapete on What should count in a company's market value?Boy, you've opened a can of worms now.
Wait for the rants & raves.
I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...
3 days ago by wykoong on Drop the egos, copy ideas, then innovateKeep up with ZDNet Asia
Facebook 
RSS Feeds 
Newsletters 
Twitter Whitepapers
Downloads
Database News
Inside ZDNet Asia
Sponsored
Echelon 2012 - The Awesomer Tech Event in Asia
Echelon 2012 – SEA’s longest running tech startup event goes Awesomer. Catch 50 of Asia’s most promising startups & over 40 international speakers on June 11-12.
Startup Asia Jakarta showcases new product-ready tech startups. Plus: hackathon, exhibition, and speakers. Use promo code CBSi50 for 50% discount.
ZDNet Asia Intelligent Singapore video series
Featuring inteviews with CXOs who define "intelligence" in their markets and reveal how their companies drive business efficiencies through ICT.
Can I specify Multiple Files?
Dear Tim,
Can I specify multiple files (with the same format) when using BULK INSERT?
What would be the syntax?
Thanks.
Multiple Files...
Did you get an answer to your multiple files question. I'm wondering the same thing.
Thanks!
RE: Can I specify Multiple Files?
That's the exact question i am here for, I am new to this but one xml file does not seem like a BULK insert, I have over 1,000 xml invoices to insert, can you specify the whole folder?