Last post Jul 12, 2011 01:06 AM by ctha6477
Aug 01, 2007 07:34 AM|krunalm|LINK
i want to know Equivalent of Load Data Infile of MySQL in SQL Server 2005?
in mysql we can direclty load file with Load Data infile query.
my question is how to do this in sql server 2005?
Aug 02, 2007 01:00 AM|krunalm|LINK
pls help me to solve this....
Aug 03, 2007 02:51 AM|Bo Chen – MSFT|LINK
You can try OPENROWSET( BULK ....) to load data directly from a file.
The Transact-SQL OPENROWSET function is a one-time method of connecting and accessing remote data. It includes all of the connection information necessary to access remote data from an OLE DB data source.
OPENROWSET can be referenced in the FROM clause of a query as though it were a table name. It can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider.
The OPENROWSET function has been enhanced in SQL Server 2005 with the addition of the
BULK rowset provider, which allows you to read data directly from a file without loading the data into a target table. This enables you to use OPENROWSET in a simple INSERT SELECT statement.
Here is one code example, It will load a photo file to a sql 2005 database:
CREATE TABLE dbo.Equipment(
EqId int NOT NULL,
Make varchar(50) NOT NULL,
Model varchar(50) NOT NULL,
EqTypeId smallint NULL,
EqDesc nvarchar(max) NULL,
EqImage varbinary(MAX) NULL
) ON PRIMARY
INSERT INTO dbo.Equipment (EqId,Make,Model,
VALUES (1, 'TB', 'DT 2005',
SET EqImage = (SELECT *
FROM OPENROWSET(BULK 'C:\My Pictures\desktop.bmp',
SINGLE_BLOB) AS a )
WHERE EqID = 1
Hope my suggestion helps
Aug 03, 2007 04:48 AM|krunalm|LINK
hi Bo Chen,
thanks for your comment.
But with OPENROWSET function - we can load any file to binary field.
if we want to load a file into blob we can use this function.
But my question is ... i have one file with text data. The file have each line with 165 chars.
so i have one table with one identity column as PK and another column as linetext
now i have to load each line in linetext field row wise.
IN MYSQL : I have loda data local infile function which loads text data row by row.
SO how to do this in SQL SERVER????
Aug 03, 2007 05:29 AM|Bo Chen – MSFT|LINK
Hi krunalm ,
Based on my understanding i don't think there is a corresponding function is MS SQL.
You can use the following method to do in MS SQL (although it's a little inconvenient)
First read that text file to a stream and then divide the text to serveral arrays. Each array contains 165 characters and you can instert those arrays into your table by a T-SQL INSERT clause.
Hope my suggestion helps
Aug 03, 2007 05:50 AM|krunalm|LINK
yes i can do it with some .net coding. but i do not want to do that.
i want to direclty load file into database. bcoz i have 3GB data file to load in a table.
i m doing that in mysql server. i can do this .net but its take good amount of time to load 3GB file and mysql does not take too much time to load file with respect to .net coding.
now i m switching to sql server and i want equivalent of this. so if i have direct query to do that then it helps a lot.
Jul 12, 2011 01:06 AM|ctha6477|LINK
what is ur solution..i also have large csv file(~3G) need to insert into MSSQL..