Bulk Import all Excel files under the Development folder into Microsoft SQL database

The following code imports all excle data from the C:\cs\ folder into the SQL database

Declare @query VARCHAR (1000)

DECLARE @max1 int

DECLARE @count1 int

DECLARE @filename varchar (100)

Set @count1 =0

CREATE TABLE #x (name varchar (200))

Insert #x exec (' Master.dbo.xp_cmdshell ' dir c:\cs\*.xls/b ')

Delete from #x where name is null

Select IDENTITY (int,1,1) as ID, name into #y from #x

drop table #x

Set @max1 = (select MAX (ID) from #y)

While @count1 <= @max1

Begin

Set @[email protected]+1

Set @filename = (select name from #y where [email protected])

Set @query = ' SELECT * to dbo.tab9 from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\cs\ ' [email protected]+ ', ' select * from [January $b11:e27] '

Print @query

EXEC @query

End

drop table #y

Go

To Dbo.tab9 from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\cs\ Shanghai. xls ', ' select * from [January $b11:e27] ')

SELECT * into DBO.TAB9 from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=c:\cs\ Shanghai. xls ', ' select * from [January $b11:e27] ')

EXEC (@query)

This article is from the "Han Li" blog, please be sure to keep this source http://91xueit.blog.51cto.com/400469/1982943

Bulk Import all Excel files under the Development folder into Microsoft SQL database