please dont rip this site

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:


file: /Techref/language/sql/tsqlimportsimpletext.htm, 3KB, , updated: 2017/1/5 18:00, local time: 2024/12/25 12:20,
TOP NEW HELP FIND: 
18.189.143.150:LOG IN

 ©2024 These pages are served without commercial sponsorship. (No popup ads, etc...).Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE. Questions?
Please DO link to this page! Digg it! / MAKE!

<A HREF="http://linistepper.com/Techref/language/sql/tsqlimportsimpletext.htm"> Import a simple text file into a new table with an id</A>

After you find an appropriate page, you are invited to your to this massmind site! (posts will be visible only to you before review) Just type a nice message (short messages are blocked as spam) in the box and press the Post button. (HTML welcomed, but not the <A tag: Instead, use the link box to link to another page. A tutorial is available Members can login to post directly, become page editors, and be credited for their posts.


Link? Put it here: 
if you want a response, please enter your email address: 
Attn spammers: All posts are reviewed before being made visible to anyone other than the poster.
Did you find what you needed?