Answer to question posed at VSLive

The thing I was most nervous about at my VSLive presentation was question time. I had an ingenious plan to avoid question time... I had timed my presentation, and it went for just over 1 hour, but in the words of a famous poet... "The best layed plans of Mice, Men (and Geeks) gang aft aglay". People from the audience stopped me mid-way through to ask questions. Most of the questions I was able to answer, but there was one that I said "I think the answer is yes, but I'll get back to you". The question was... "Does SQL Express support bulk copy?", and after some testing the answer is "Yes". As mentioned in my presentation (slides available here) SQL Express does not support SSIS (The replacement for DTS), however after testing, the bcp utility works fine. The following script can be used to verify that both import and export work fine.


use tempdb

-- Create a test table
create table BulkTest
(TestKey INT NOT NULL,
TestData NVARCHAR(50) NULL,
CONSTRAINT BulkTest_PK_Constraint PRIMARY KEY (TestKey ASC)
)

-- Add some test data
INSERT INTO BulkTest
(TestKey, TestData)
VALUES
(1, 'This is just some test data')

INSERT INTO BulkTest
(TestKey, TestData)
VALUES
(2, 'This is some more test data')

INSERT INTO BulkTest
(TestKey, TestData)
VALUES
(3, 'It is important to have test data')

-- perform bulk Export using bcp command line utility
-- bcp tempdb.dbo.BulkTest out BulkTest.dat -T -c -S ServerName\SQLExpress

delete from BulkTest

-- perform bulk Import using bcp command line utility
-- bcp tempdb.dbo.BulkTest in BulkTest.dat -T -c -S ServerName\SQLExpress

select * from BulkTest