Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to import a large file into SQL server that is | delimited.

I know basically nothing about the data, I just want to get it imported.

When I go to Database->tasks->import I use the advanced option to suggest types, and provide padding.

The problem is that that routine does not go through the whole file, even when I specify an absurly large number of rows (1000000000), and so I am constantly getting tructation->error, change the types, restart the import etc errors.

Is there a better way to do this?

Note: The file is not on the same machine as SQL Server

share|improve this question
add comment

closed as unclear what you're asking by Paul White, StanleyJohns, Mark Storey-Smith, RolandoMySQLDBA, dezso Aug 4 '13 at 6:46

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

I would use Bulk insert. If you have a delimited file, it should be pretty easy.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

A sample looks like this

BULK INSERT dbo.MyTable
   FROM 'c:\test\test.txt'
   WITH 
      (
         FIELDTERMINATOR ='|',
         ROWTERMINATOR =' \n'
      );

EDIT:

Based on the given requirements (the file cannot be copied, and a share cannot be used), My recommendation would be to develop your own SSIS package. In the package you will be able to choose your source and play with the data types. The suggest types seems to be a constant miss when I have used it, so I wouldn't rely on that. I would look at a row of the data in Excel and determine, what data type and how large you should make it. Sorry there is no silver bullet here.

Note: You can go through the import wizard and save the package, if you want to start from that point, instead of starting from scratch.

share|improve this answer
    
This does not work as the file is not on the same machine as the server (see note) –  soandos Aug 2 '13 at 18:54
    
Can you copy the file to the database server, or put the file on a share that the database server can see? –  Adam Haines Aug 2 '13 at 18:58
    
No, it is not allowed –  soandos Aug 2 '13 at 19:02
1  
The silver bullet is going to be the BULK INSERT command, but in order for this to work the SQL Server Service account has to be able to see the file (if you are using SQL Authentication) or you will have to perms the file (Windows Authentication)... What I am confused about is how you can see the file from the import wizard, but not from Bulk insert, if the file is not on the db server and not on a files share. –  Adam Haines Aug 2 '13 at 19:20
1  
@AdamHaines The file is on a local machine, not a network share. The import wizard is from SQL management studio, not sql server –  soandos Aug 4 '13 at 1:32
show 4 more comments

Not the answer you're looking for? Browse other questions tagged or ask your own question.