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.
This question is related to
sql
sql-server
csv
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/
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
Source: Stackoverflow.com