Search code examples
sql-servert-sqlaliasopenrowset

Insert text files to database using OPENROWSET

I have bunch of text files that have phone numbers separated by carriage return. I know these are all numbers. I want to insert all of these numbers into a table in my SQL database. but the problem is the text file starts with the first number; not a column name. It means that I don't know the column name each time I try to insert data.

To clarify this I show you the SQL I've written:

INSERT into dbo.IranCellPhoneNumbers
SELECT PostalCode = null, mobile as Number,CityId= 20
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=D:\Projects\Sepanta\ExcelImporter\ExcelImporter\bin\Debug\k1\,
'SELECT * FROM etebari.txt')

I set postalCode to null because I want the table definitions to match. and set CityId hard coded because I know which city I mean. but the problem is the number column.. it does not match because the text file starts with a number not "mobile" column name.

If I want this code to work, before insertion I have to open up these text files and add "mobile" at the top of list and this is not desirable.

What do you propose?

Solution

  • You don't need a column name. Just do this:

    INSERT into dbo.IranCellPhoneNumbers
SELECT PostalCode = null, *, CityId= 20
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=D:\Projects\Sepanta\ExcelImporter\ExcelImporter\bin\Debug\k1\;HDR=NO',
'SELECT * FROM etebari.txt')