TransactSQL:

Import a simple text file into a new table with an id

USE [ecom]
GO
/****** Object:  StoredProcedure [dbo].[Load_ASCII_File]    Script Date: 12/20/2016 17:32:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		James Newton
-- Create date: 2016/12/16
-- Description:	Load ASCII file with index
-- Version 1: Works but requires unsafe permissions for the user
-- =============================================
-- Note user will need execute permissions for this stored procedure.
ALTER PROCEDURE [dbo].[Load_ASCII_File] 
	@FileName AS Varchar(100)
WITH EXECUTE AS 'textimportuser'
	-- the user executing this procedure must have impersonate rights from textimportuser
AS
BEGIN
	--DROP TABLE IF EXISTS [dbo].[Text] -- only on SQL Server 2016 and up.
	IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Text' AND TABLE_SCHEMA = 'dbo')
	    DROP TABLE [dbo].[Text];
	-- User will need delete permissions for that table, but once deleted, those permissions are lost, so instead...
	-- User will need alter permissions for the dbo schema.

	CREATE TABLE [dbo].[Text] ( text nvarchar(255) NULL ) ON [PRIMARY];
	-- User will need alter permissions for the dbo schema.

	--BULK INSERT [dbo].[Text] FROM 'G:\temp\sqlimport\'+@FileName WITH (FIELDTERMINATOR='\0',ROWTERMINATOR='\n'); 
	-- Nope, we have to do it dynamically in order to add the path here... How much does that suck?
	-- http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure

	DECLARE @Bulk varchar(MAX);
	SET @Bulk = 
	'BULK INSERT [dbo].[Text] FROM ''D:\temp\sqlimport\'+ @FileName +
	''' WITH (FIELDTERMINATOR=''\0'',ROWTERMINATOR=''\n''); ';
	-- Note that the user SQLServer2005MSSQLUser#databasename must have full permissions for a folder 
	-- from which it will import or restore data. Which is why we really want to limit this to that folder.

	EXEC(@Bulk);
	-- The login of the user will need bulkadmin permission to do the bulk insert. Not the user... the login... server wide.
	-- http://stackoverflow.com/questions/14604416/how-to-enable-bulk-permission-in-sql-server
	-- and even with all those permissions, it probably still won't let this shit run. 

	-- Now add an index
	ALTER TABLE [dbo].[Text] ADD id int NOT NULL IDENTITY (1, 1);

END

/* or INSERT INTO with OPENROWSET and BULK:
INSERT INTO dbo.text
    SELECT *
    FROM OPENROWSET (
	DATABASE
        FIELDTERMINATOR='\0',
        ROWTERMINATOR='\n'
        ) AS t1;
*/

See also:

• https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/
• https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/
• http://stackoverflow.com/questions/24739887/bulk-insert-where-in
• http://www.sommarskog.se/arrays-in-sql-2005.html

  .