using BULK INSERT how to insert data into table having 5 columns and text file having 3 columns with unmatched columns(table and text file)

Question

using BULK INSERT how to insert data into table having 5 columns and text file having 3 columns with unmatched columns(table and text file)

Nagaram

SSCrazy

Points: 2110
More actions
November 29, 2010 at 4:46 am

#142759

how to insert data into table having 5 columns and text file having 3 columns.
Example:
Table has id,name,age,salary,[phone number] columns and
text file having values for id,salary.age fields.
how to match text field columns with table fields using bulk insert with format files.
can any one please reply.
Thanks in advance!!!

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

it seems to me that unless you want the name and phone number fields to be null, you have to join to another table (by ID?) to get those other two columns, right?

also why are you importing age instead of the DOB so the age can be calulated on demand? if my birthday is tomorrow, then your age column is already incorrect.

so you'll need to BULK INSERT into a temporary table that has the same three fields as the text file, then insert from that temp table into the final table;

how to insert data into table having 5 columns and text file having 3 columns.

Example:

Table has id,name,age,salary,[phone number] columns and

text file having values for id,salary.age fields.

bCREATE TABLE #BULKDATA(id int ,salary money,age int )

BULK INSERT BULKACT FROM 'c:\Export_o.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 1

)

INSERT INTO FinalTable(id,name,age,salary,[phone number] )

SELECT

#BULKDATA.id,

OtherTable.name,

#BULKDATA.salary,

#BULKDATA.age

OtherTable.,[phone number]

FROM #BULKDATA

INNER JOIN OtherTable ON OtherTable.id = #BULKDATA.ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!