How do I BULK INSERT only one or some of the fields in the row?

Question

Post reply

How do I BULK INSERT only one or some of the fields in the row?

mukundbtech-541638

Old Hand

Points: 341
More actions
March 11, 2008 at 4:12 am

#71191

i tried to insert a single column from a txt file a sql DB say the file contains
1 tab field2 tab field3
2 .................
3 ..........etc
create table #aBSearchbTempcTableB
(
ItemID bigint null,
UnUsedDataVARCHAR(50)null
)
BULK INSERT
#aBSearchbTempcTableB
from
'\\ppdys1402\Share\ids.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
--FIRSTROW = 2
)
this helps me to do that .. here my delimeter is tab so if many fields are there i can easily import the 1st column into that serverDB .. But i have situation where the file can contain only ids
for eg without tab
ie
1enter
2..
3..
now here no tab delimiter is there .. now i need a script that can import a 1st column into serverDB even if the txt file contains one column or more that one column

Viewing 15 posts - 1 through 15 (of 21 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

You need to use a BCP format file. See Books Online for how to build one... it's not that hard.

--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)

mukundbtech-541638 Old Hand Points: 341 More actions · Answer 2

Thank u .. and like to know is there any other option other than using a format file .. if so help me

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Sure, you could write a VBS script, a smidgen of C# or bit of Java code, or any other number of options. ALL of them will be slower than BCP with a format file... sometimes, MUCH slower. A very well Java program will import 1.2 million rows 12 fields wide in only about 16 minutes with no validation to speak of. BCP will do the same thing, trap bad rows in a file, and do some other minor validations for length and the like in only 51 seconds.

So, make the choice... it's your data... But I think you not spending the required 10-15 minutes to make the format file would be a mistake on your part... takes that long to write the Java... longer if you check it into source control.

--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)

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Jeff, I have found that information helpful to a format file newbie is hard to come by. Maybe you know of an article that explains it a bit better than BOL?

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Greg Snidow (3/14/2008)
Jeff, I have found that information helpful to a format file newbie is hard to come by. Maybe you know of an article that explains it a bit better than BOL?Greg

BOL actually has one of the better explanations... but, if there's nothing private in it, attach a sample of your file and a record layout and lemme show you how it's done...

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

mukundbtech (3/13/2008)
Thank u .. and like to know is there any other option other than using a format file .. if so help me

Rethinking this, DTS would probably be the easiest for you... it won't be the fastest, but if might be the easiest.

--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)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

1enter
2..
3..
now here no tab delimiter is there .. now i need a script that can import a 1st column into serverDB even if the txt file contains one column or more that one column

If there is only one column in your text file then your field terminator can be anything that does not appear in the data (ie: , | tab). You just need to have your row terminator set correctly to either a line feed or carriage return or both. The bcp process will run into the row terminator before the field terminator and understand that there is only one column.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (3/15/2008)
mukundbtech (3/13/2008)
Thank u .. and like to know is there any other option other than using a format file .. if so help me
Rethinking this, DTS would probably be the easiest for you... it won't be the fastest, but if might be the easiest.

And more specifically - the "import and Export data" wizard (which automates DTS). Assuming you're doing a "straight" import - it should be fairly performant (not quite as good as the command-line options, but still - pretty darn fast). If you're worried about data quality - go with the "create table" option on the destination side: once it's done bringing it in - then use T-sql to finish cleaning up the data and then push it into your production table.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mukundbtech-541638 Old Hand Points: 341 More actions · Answer 9

attached are my input files ... previously i used Bulkinsert method when our project requirement is to import only a single column file(my first attch) values .. then when they ask for multicolumn (my second attach)i implemented as i given in first comment . But now the requirement is to use both the single and multicolumn and now i have implemented in the clientside code to read the values and send to the SP.but i like to know other than using extra files like BCP format or client side code or DTS , is there we can implement in a single stretch like using bulkinsert .

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

You can bcp your whole file into a staging/temp table and then use an insert statement to append the specific fields you want into your main table. Not the most efficient method but meets your requirements of no format file and no DTS.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

mukundbtech (3/20/2008)
but i like to know other than using extra files like BCP format or client side code or DTS , is there we can implement in a single stretch like using bulkinsert .

Yes there is... look up "sp_AddLinkedServer" in Books Online and refer to example "H. Use the Microsoft OLE DB Provider for Jet to access a text file"

You can also take a look at OpenRowset...

--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)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 12

Jeff Moden (3/20/2008)
Yes there is... look up "sp_AddLinkedServer" in Books Online and refer to example "H. Use the Microsoft OLE DB Provider for Jet to access a text file"
You can also take a look at OpenRowset...

Doesn't sp_AddLinkedServer require a schema.ini file for text files like a bcp format file?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Nope... here's the example from BOL... and, I've tested it... works fine... and despite what it says about schema.ini, you don't need it for comma delimited files.

H. Use the Microsoft OLE DB Provider for Jet to access a text file

This example creates a linked server for directly accessing text files, without

linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0

and the provider string is 'Text'.

The data source is the full pathname of the directory that contains the text files.

A schema.ini file, which describes the structure of the text files, must exist in the

same directory as the text files. For more information about creating a schema.ini

file, refer to Jet Database Engine documentation.

--Create a linked server

EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'c:\data\distqry',

NULL,

'Text'

GO

--Set up login mappings

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL

GO

--List the tables in the linked server

EXEC sp_tables_ex txtsrv

GO

--Query one of the tables: file1#txt

--using a 4-part name

SELECT *

FROM txtsrv...[file1#txt]

The really cool thing is... it'll read "ragged right delimited files"... try it...

--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)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 14

Maybe I'm being thick but doesn;t that say a schema.ini file must be in the same directory as the file being imported?