Import CSV into SQL Server (including automatic table creation)

Question

I have several CSV files, which I want to import into an SQL Server database. I know if this is possible with BULK insert, but I want a solution, so that also the import table is automatically created before on basis the first row of the CSV files,are the column names.

Do you need a program to do this? You can use SQL Server Management Studio import tool. — 03Usr, Mar 27 '13 at 09:27

Nenad Zivkovic · Accepted Answer · 2017-08-22T08:54:04.343

33

SQL Server Management Studio provides an Import/Export wizard tool which have an option to automatically create tables.

You can access it by right clicking on the Database in Object Explorer and selecting Tasks->Import Data...

From there wizard should be self-explanatory and easy to navigate. You choose your CSV as source, desired destination, configure columns and run the package.

If you need detailed guidance, there are plenty of guides online, here is a nice one: http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

edited Aug 22 '17 at 08:54

answered Mar 27 '13 at 09:39

Nenad Zivkovic

18,221
6
42
55

4

This link does not cover CSV files, which was the question. – Ryan Apr 29 '13 at 18:10
6

@rncrtr I can assure you process is almost the same. There is a slight difference is some options after selecting flat-file as source instead of excel from the described, but wizard is good enough so it shouldn't be a problem to understand. – Nenad Zivkovic Apr 29 '13 at 20:45
There isn't a flat file option in the server I'm using, so this is somewhat of a dead end for me. I found out there is a separate tool that does contain it (Import and Export Data Tool), but it took a while to find out that was the ticket to use. Thanks. – Ryan Apr 29 '13 at 21:05
1

Open MS Excel, navigate to "Data" tab, "From Text" option. Make your ways through simple settings to import a csv file to your excel sheet. Save it as xls. Use the option mentioned in the answer above. Revel in the Magic! – Chiranjib Mar 08 '16 at 11:39
Wow, excellent answer. I went from googling around, to here, to having my data ready to query in SQL Server within just a few minutes. – Graham Apr 08 '16 at 19:38
@NenadZivkovic, wanna update the answer with the actual steps? – Mikael Dúi Bolinder Aug 22 '17 at 08:47
This did not work for me. "Error inserting data into table. (Microsoft.SqlServer.Import.Wizard) Object reference not set to an instance of an object. (Microsoft.SqlServer.Prose.Import)" – barlop Mar 29 '18 at 08:15

score 8 · Answer 2 · edited Nov 08 '15 at 06:28

8

You can create a temp table variable and insert the data into it, then insert the data into your actual table by selecting it from the temp table.

 declare @TableVar table 
 (
    firstCol varchar(50) NOT NULL,
    secondCol varchar(50) NOT NULL
 )

BULK INSERT @TableVar FROM 'PathToCSVFile' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
GO

INSERT INTO dbo.ExistingTable
(
    firstCol,
    secondCol
)
SELECT firstCol,
       secondCol
FROM @TableVar

GO

edited Nov 08 '15 at 06:28

Pure.Krome

84,693
113
396
647

answered Mar 27 '13 at 09:35

03Usr

3,335
6
37
63

2

Why wouldn't you just insert it into the existing table directly? – Travis Feb 21 '14 at 20:36
1

I found you need to use a temp table rather than a table variable. See this [answer](http://stackoverflow.com/a/14113855/469637) – Jeff Aug 21 '14 at 00:08
5

Cannot bulk insert into a `table variable`. has to be a temp table or a real table. – Pure.Krome Nov 08 '15 at 06:29
4

This does not automatically create the table from the file, which was the entire point of the question. – underscore_d Jul 27 '17 at 09:40
1

hi, how could we make this a dynamic table based on the headers in the CSV file – aggie Nov 15 '18 at 02:52

Import CSV into SQL Server (including automatic table creation)

2 Answers2

Linked