Select data from flat file to sql table

Question

Post reply

Select data from flat file to sql table

vipin_jha123

Hall of Fame

Points: 3085
More actions
August 5, 2015 at 12:35 am

#310533

Hi,
I have TXT file in below folder
H:\Spectrum_V2_ETL\Loading_Geography_Table_Data\Loading_Geography_Table_Data\emp.txt
Now I want to load data from flat file into sql table using Stored procedure,
Could you help me in that to provide the proper syntax for same.
Regards,
Vipin Jha

Viewing 5 posts - 1 through 4 (of 4 total)

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

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 1

Hi When I am trying to execute below command I am getting error

BULK INSERT ##MD_GEOGRAPHYLEVELTYPE

FROM 'H:\Spectrum_V2_ETL\Loading_Geography_Table_Data\Loading_Geography_Table_Data\MD_GEOGRAPHYLEVELTYPE_US.txt'

WITH ( FIELDTERMINATOR =',', FIRSTROW = 9 )

error

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "H:\Spectrum_V2_ETL\Loading_Geography_Table_Data\Loading_Geography_Table_Data\MD_GEOGRAPHYLEVELTYPE_US.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

Emil B SSCertifiable Points: 5568 More actions · Answer 2

You can use BULK INSERT

have a look here https://msdn.microsoft.com/pl-pl/library/ms188365(v=sql.110).aspx

Emil B SSCertifiable Points: 5568 More actions · Answer 3

vipin_jha123 (8/5/2015)
Hi When I am trying to execute below command I am getting error
BULK INSERT ##MD_GEOGRAPHYLEVELTYPE
FROM 'H:\Spectrum_V2_ETL\Loading_Geography_Table_Data\Loading_Geography_Table_Data\MD_GEOGRAPHYLEVELTYPE_US.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 9 )
error
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "H:\Spectrum_V2_ETL\Loading_Geography_Table_Data\Loading_Geography_Table_Data\MD_GEOGRAPHYLEVELTYPE_US.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

make sure that sql server service account have access to this path.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

"The system cannot find the path specified" - that's a very clear error message. Is the file on the same computer as the database? If not, you'll need to provide the path in URL format (\\MyServer\MyShare\MyPath\MyFile.txt), and make sure that the SQL Server service account has access.

John