Bulk Insert from file to table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Bulk Insert from file to table

Bulk Insert from file to table

Rate Topic

Display Mode

Topic Options

Author

Message

mupparaju78

Posted Monday, January 28, 2013 2:48 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:04 AM
Points: 20, Visits: 558

Hi, I have .dat file with following format data.

1,2,3,
4,5,6,
7,8,9,

i want load this format file into sql table using bulk insert statement.

how can we fit ROWTERMINATOR in bulk statement.

please let me know....

Post #1412280

anthony.green

Posted Monday, January 28, 2013 3:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Use the ROWTERMINATOR option of the BULK INSERT command.

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

, \ n

or something along them lines, just remove the spaces.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412308

mupparaju78

Posted Monday, January 28, 2013 4:31 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:04 AM
Points: 20, Visits: 558

i tried with .. but no luck

Post #1412341

anthony.green

Posted Monday, January 28, 2013 4:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

bulk insert ... from '...' with (fieldterminator = ',', rowterminator = ', \ n') --Remove the spaces between , \ n

Need to ensure that there is a carrage return at the end of the last line in the file

1,2,3,(CR)
4,5,6,(CR)
7,8,9,(CR)

Post #1412347

mupparaju78

Posted Monday, January 28, 2013 11:03 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:04 AM
Points: 20, Visits: 558

Hi anthony.green,

Thank you for your reply.

when i opend the file and delere " ," and type again... at that move ment your functionality is working.

with out open file.. all rowsdata insrted into single row.....

is there any functionality to edit the last charecter in file

Post #1412762

anthony.green

Posted Tuesday, January 29, 2013 1:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Try ROWTERMINATOR of '','+CHAR(10)+''', will need to build it dynamic, might be getting confused as relates to line feed carrage return

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT Table
FROM ''C:\file.txt''
WITH (ROWTERMINATOR = '','+CHAR(10)+''', FIELDTERMINATOR = '','')';
EXEC (@bulk_cmd);

Post #1412816

« Prev Topic | Next Topic »

Permissions