Inputting data to a file

[Flybridge]

As a part time techie I normally only have to use SELECT or UPDATE commands, but I may need to transfer data from a file to an SQL Server table.

I have experimented but can't seem to get it to work.

What is the structure of the command to transfer data into an SQL table, starting with the SELECT statement to get it from the source file?

Thanks


'The world isn't round - it's bent!' Spike Milligan

 

[SQLDenis]

INSERT INTO Yourtable
SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;',
'select * from FileName.txt')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

or this way

BULK INSERT Yourtable
FROM 'c:\FileName.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mhoyt]

Or use Enterprise Manager.

Go to the database where you want it. Tools - Wizards - DTS Wizard - Import.

You'll have options for all sorts of different import file formats. Delimited text or Excel work well.

It will create a new table in SQL, even naming the fields for you (based on first row of your file).