Importing text data into a table using Microsoft SQL Server Management Studio Express?

Question

I have just started using Microsoft SQL Server Management Studio Express and want to populate the contents of a text file into a new table. Can Microsoft SQL Server Management Studio Express create a linked table like MS Access?

Answer 1

The way I normally do this is to use the import data function to create a new table. Right-click on the database you want to import the data into, and choose All Tasks|Import data. This will bring up a wizard.



On the first tab, choose flat file or text file (depending on the version of SQL Server). The next tab should let you define the attributes of your file -- does the first row contain column data; how are the columns delimited; text specifications. Further along the process you can specify the table name and any mapping that has to happen.



I find that it only takes a few minutes to work through the process, but then I've had to do this many times a week for several months.



If you're doing this sort of thing regularly, you may want to look into setting up an SSIS package (SQL Server Integration Services) which can bring in data on a schedule. It can be very slick if you have the time and inclination to set it up.

Answer 2

You don't need BIDS. From Management Studio, right click on the database that contains the table you want to export. The menu will have an option of 'Tasks', hover over tasks, then choose Export Data (near the bottom). This will open the export wizard. The first page is the data source, which should be set to what you have open; change this if you want a table from another database. The second window is the destination; from Data source, choose Microsoft Excel, then the path where you want the spreadsheet saved. Type in the name of the spreadsheet if you are creating one from scratch. The third page is where you can either choose a whole table/view or write a query.

Answer 3

I suppose you'll can do it with BULK INSERT statement.