How to bulkinsert a particular column to sql table

Question

How to bulkinsert a particular column to sql table

nithiyanandam-447806

Ten Centuries

Points: 1197
More actions
August 30, 2010 at 6:20 am

#239696

Hi,
i Need your help for the following
sql table script looks like
create table emp
(Eno int,
FName varchar(50),
LName varchar(50),
Address varchar(50),
City varchar(50),
Pin varchar(10),
Status Null
)
i need to bulk insert data from a txt file to this table. but the txt file having
Eno ,
FName ,
LName ,
Address ,
City ,
Pin columns only .
after insert these values, we will update the status column.
but we can't bulk insert the table..
it expects status columns.
bulkinsert query is
bulk insert test.dbo.emp
from 'E:\testdata.txt'
with (
fieldterminator = '|',
rowterminator = ''
)
go
thanks in advance...
Nithi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I know that you can use format file to specify which columns should be inserted (you can read about it in BOL or Google). I think (but I didn’t test it yet), that you can also create a query that references only the columns that you want to insert the data, and then run the bulk insert statement without a format file and use the view instead of the table.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

nithiyanandam-447806 (8/30/2010)
but we can't bulk insert the table..
it expects status columns.

Obviously, you've correctly identified the problem. You'll need to change something about that. My recommendation is to NEVER bulk insert from the outside world directly into a permanent table. Always insert into a STAGING table first so you can do things like validate the data and add things like the statuses you require.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions