SQL Server 2005 - Work with Flat Files

--The best cenario is a table with no indexes, no replication, no constraints or triggers and the recovery model
--The recommended Recovery Model is: bulk-load 

--Table if no replicated
--TABLELOCK hint used
--atention for the table

--change the recovery model
select databasepropertyex('Database_name','Recovery')
alter database Database_name set recovery bulk_logged;

alter database Database_name set recovery Bulk_logged
select databasepropertyex ('Database_name','Recovery')

--It's important to remenber:

--Running the bcp-Bulk Copy Programn
--bcp has limited data-transformation capabilities
--bcp has limited error handling capabilities.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE database_name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tablçe') AND type in (N'U'))
DROP TABLE the_table --drop the table if exists

CREATE TABLE [dbo].[Exam](
[ExamID] [int] NULL,
[ExamName] [varchar](50) NULL,
[ExamDescription] [varchar](50) NULL,
[ExamXML] [xml] NULL
) ON [PRIMARY]

select * from (table_name) -- in this case dbo.exam

--the bcp command (Bulk Copy Command)
bcp [[FileImportDB.][dbo].[Exam] in d:\FileImportFiles\ExamImportFile.txt -T -c -- the filename


--the bulk insert
bulk insert FileImportDB..Exam from 'D:\FileImportFiles\ExamImportFile.txt' with (tablock)--lock the table during the import
select * from dbo.exam


--OPENROWSET
UPDATE EXAM
SET ExamXML = (SELECT A.Col1
FROM OPENROWSET(
BULK 'D:\FileImportFiles\ExamImportFile.txt',
SINGLE_BLOB) AS A(Col1))
WHERE ExamID=1
select * from dbo.exam