/*
BULK INSERT tablename
FROM 'n:\employee.csv' --This is CSV file
WITH ( FIELDTERMINATOR ='|',rowterminator = '\n',FIRSTROW = 1 )
*/
--select * from tablename
--drop table tablename
alter proc createtable
@path nvarchar(2000),@outTableName nvarchar(2000)
as
--https://stackoverflow.com/questions/4344093/creating-table-with-the-same-columns-as-in-a-csv
begin
declare @execSQL nvarchar(4000)
declare @tempstr varchar(4000)
declare @col varchar(4000)
declare @table nvarchar(4000)
-- Create a temp table to with one column to hold the first row of the csv file
CREATE TABLE #tbl (line VARCHAR(8000))
SET @execSQL =
'BULK INSERT #tbl
FROM ''' + @path + '''
WITH (
FIELDTERMINATOR =''|'',
FIRSTROW = 1,
ROWTERMINATOR = ''\n'', --or ''0x0a'' for linux type files
LASTROW = 1
)
'
EXEC sp_executesql @stmt=@execSQL
select * from #tbl
SET @col = ''
SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), '|')) FROM #tbl)
DROP TABLE #tbl
WHILE CHARINDEX('|',@tempstr) > 0
BEGIN
SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX('|',@tempstr)-1))) + ' varchar(100),'
SET @tempstr = SUBSTRING(@tempstr, CHARINDEX('|',@tempstr)+1, len(@tempstr))
END
PRINT @col
SET @col = @col + @tempstr + ' varchar(100)'
if object_id(@outTableName) is not null
exec dbo.procdroptable @TableName=@outTableName
SET @table = 'create table '+@outTableName + ' (' + @col + ')'
EXEC sp_executesql @stmt=@table
-- Load data from csv
SET @execSQL =
'BULK INSERT '+ @outTableName +'
FROM ''' + @path + '''
WITH (
FIELDTERMINATOR =''|'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)
'
EXEC sp_executesql @stmt=@execSQL
end
/*
ALTER PROCEDURE dbo.procdroptable
@TABLENAME SYSNAME
AS
--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
EXEC sp_executesql @SQL;
END
GO
*/
/*
BULK INSERT tablename
FROM 'n:\employee.csv' --This is CSV file
WITH ( FIELDTERMINATOR ='|',rowterminator = '\n',FIRSTROW = 1 )
*/
--select * from tablename
--drop table tablename
alter proc createtable
@path nvarchar(2000),@outTableName nvarchar(2000)
as
--https://stackoverflow.com/questions/4344093/creating-table-with-the-same-columns-as-in-a-csv
begin
SET QUOTED_IDENTIFIER ON
declare @execSQL nvarchar(4000)
declare @tempstr varchar(4000)
declare @col varchar(4000)
declare @table nvarchar(4000)
-- Create a temp table to with one column to hold the first row of the csv file
CREATE TABLE #tbl (line VARCHAR(8000))
SET @execSQL = >
'BULK INSERT #tbl
FROM ''' + @path + '''
WITH (
FIELDTERMINATOR =''|'',
FIRSTROW = 1,
ROWTERMINATOR = ''\n'',
LASTROW = 1
)
'
EXEC sp_executesql @stmt=@execSQL
select * from #tbl
SET @col = ''
SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), '|')) FROM #tbl)
DROP TABLE #tbl
WHILE CHARINDEX('|',@tempstr) > 0
BEGIN
SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX('|',@tempstr)-1))) + ' varchar(100),'
SET @tempstr = SUBSTRING(@tempstr, CHARINDEX('|',@tempstr)+1, len(@tempstr))
END
--PRINT @col
if LEN(@tempstr) > 1
BEGIN
SET @col = @col + @tempstr + ' varchar(100)'
END
if object_id(@outTableName) is not null
exec dbo.procdroptable @TableName=@outTableName
SET @table = 'create table '+@outTableName + ' (' + @col + ')'
EXEC sp_executesql @stmt=@table
-- Load data from csv
SET @execSQL =
'BULK INSERT '+ @outTableName +'
FROM ''' + @path + '''
WITH (
FIELDTERMINATOR =''|'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)
'
EXEC sp_executesql @stmt=@execSQL
end
/*
ALTER PROCEDURE dbo.procdroptable
@TABLENAME SYSNAME
AS
--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
EXEC sp_executesql @SQL;
END
GO
*/