-- Create sample table and indexes

CREATE TABLE testtable (

[col1] [int] NOT NULL primary key clustered,

[col2] [int] NULL,

[col3] [int] NULL,

[col4] [varchar](50) NULL);

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO testtable (col1, col2, col3, col4)

VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))

SELECT @val=@val+1

END

GO

-- Create a format file

bcp sw_test.dbo.testtable format nul -c -t, -f c:\testtable_formatfile.fmt -T

bcp sw_test.dbo.testtable out c:\testtable.dat -c -t, -f c:\testtable_formatfile.fmt -T -S

--1. BULK load

truncate table testtable

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt');

--2. BULK load with tablock

truncate table testtable

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

--3. BULK load with tablock and drop/recreate indexes

truncate table testtable

DROP INDEX testtable.idx_testtable_col2;

DROP INDEX testtable.idx_testtable_col3;

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

--4. BULK load with tablock and drop/recreate indexes and change recovery model

truncate table testtable

ALTER DATABASE SW_TEST SET RECOVERY SIMPLE;

DROP INDEX testtable.idx_testtable_col2;

DROP INDEX testtable.idx_testtable_col3;

BULK INSERT dbo.testtable

FROM 'C:\testtable.dat'

WITH (FORMATFILE = 'C:\testtable_formatfile.fmt', TABLOCK);

CREATE INDEX idx_testtable_col2 on testtable (col2 asc);

CREATE INDEX idx_testtable_col3 on testtable (col3 asc);

ALTER DATABASE SW_TEST SET RECOVERY FULL;