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; */
|file: /Techref/language/sql/tsqlimportsimpletext.htm, 3KB, , updated: 2017/1/5 19:00, local time: 2018/4/26 06:35,
|©2018 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?|
<A HREF="http://www.piclist.com/techref/language/sql/tsqlimportsimpletext.htm"> Import a simple text file into a new table with an id</A>
|Did you find what you needed?|
PICList 2018 contributors:
o List host: MIT, Site host massmind.org, Top posters @20180426 RussellMc, Van Horn, David, Sean Breheny, Isaac M. Bavaresco, Bob Blick, Neil, David C Brown, alan.b.pearce, Brent Brown, John Gardner,
* Page Editors: James Newton, David Cary, and YOU!
* Roman Black of Black Robotics donates from sales of Linistep stepper controller kits.
* Ashley Roll of Digital Nemesis donates from sales of RCL-1 RS232 to TTL converters.
* Monthly Subscribers: Gregg Rew. on-going support is MOST appreciated!
* Contributors: Richard Seriani, Sr.
Welcome to www.piclist.com!