CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlbulkinsert
wootscootinboogie
wootscootinboogie

Reputation: 8695

Ignore certain columns when using BULK INSERT

I have a comma delimited text file with the structure

field1   field2   field3   field4
1        2        3        4

I wrote the following script to bulk insert the text file, but I wanted to leave out column 3

create table test (field1 varchar(50),field2 varchar(50),field4 varchar(50))
go
bulk insert test
from 'c:\myFilePath'
with 
(fieldterminator=',',
rowterminator='\n'
)

The insert worked fine, but the results of the insert made field4 look like field3,field4, so the field 3 was actually just concatenated onto field4. The flat files I'm working with are several gigs and can't be easily modified. Is there a way to use bulk insert but have it ignore the columns that aren't declared in the create table statement?

Upvotes: 14

Views: 74197

Answers (5)

user2713404
user2713404

Reputation: 1

You can indeed ignore certain data fields - see this:

Use a format file like here (Example): enter image description here

Essentially you map the unwanted source data field to column 0 in your table. Yes FMT files are wierd see this Stack Overflow article The fmt file maps data fields to columns by ordinal. column 1 is the source data field order column 6 is target table column ordinal. These are 1 based numbered, and 0 means map to no column.

Upvotes: -1

IHAFURR
IHAFURR

Reputation: 161

You can create a temporary table and insert any data there. After that, you can do whatever you want with it.

CREATE TABLE #TmpTable(
    [CategoryId] [int] IDENTITY(1,1) NOT NULL,
    [ParentCategoryId] [int] NULL,
    [CategoryTypeId] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Code] [varchar](255) NOT NULL,
    [AlternateName] [varchar](900) NOT NULL
)

BULK INSERT [dbo].[#TmpTable]
FROM 'C:\tmp\Categories.csv'
WITH
(
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '0x0a'   --Use to shift the control to next row         
)

-- original table insert
INSERT INTO [dbo].[Categories]
( 
    CategoryId,
    ParentCategoryId,
    CategoryTypeId,
    Name,
    Code,
    AlternateName,
    IsDeleted,
    SystemCreated
)
SELECT 
    CategoryId,
    ParentCategoryId,
    CategoryTypeId,
    Name,
    Code,
    AlternateName,
    0,           -- custom value missed by file
    GETDATE()    -- custom value missed by file
FROM #TmpTable

-- remove tmp table
DROP TABLE #TmpTable

Upvotes: 3

Charles Okwuagwu
Charles Okwuagwu

Reputation: 10866

The easiest way is to create a view that has just the columns you require.

Then bulk insert into that view.

Example:

create table people (name varchar(20) not null, dob date null, sex char(1) null)

--If you are importing only name from list of names in names.txt

create view vwNames as
select name from people

bulk insert 'names.txt'

Upvotes: 10

user2418601
user2418601

Reputation: 51

you cant ignore a field while doing bulk insert , insted of doing that .. Load all 4 column and drop the colum which you dont want 

create table test (field1 varchar(50),field2 varchar(50), field3 varchar(50),field4 varchar(50))
go
bulk insert test
from 'c:\myFilePath'
with 
(fieldterminator=',',
rowterminator='\n'
)

ALTER TABLE test DROP column [field3]

Upvotes: 5

Janine Rawnsley
Janine Rawnsley

Reputation: 1258

You can use a format file to do this:

http://msdn.microsoft.com/en-gb/library/ms178129.aspx

http://msdn.microsoft.com/en-gb/library/ms179250.aspx

Or if you want a slightly cheekier way, just import it all and drop a column afterwards. ;)

Upvotes: 10

Related Questions