I am using the most recent version, 9.5.12.10845 Here is the full script (Format SQL works if the line with TRIM() in it is commented out) USE [database1] GO /****** Object: StoredProcedure [dataimport].[FileToTable] Script Date: 8/6/2019 4:16:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dataimport].[FileToTable] @filepathandname [nvarchar](255), @DBName [nvarchar](128), @TableSchema [nvarchar](128), @TableName [nvarchar](128), @addExtraColumns [bit] = 1 WITH EXECUTE AS CALLER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF @filepathandname LIKE '"%"' SET @filepathandname = SUBSTRING(@filepathandname, 2, LEN(@filepathandname) - 2); DECLARE @FILENAME NVARCHAR(255) = REVERSE(LEFT(REVERSE(@filepathandname), CHARINDEX('\', REVERSE(@filepathandname)) - 1)); DECLARE @PATH NVARCHAR(255) = LEFT(@filepathandname, PATINDEX('%' + @FILENAME, @filepathandname) - 1); DECLARE @sql VARCHAR(MAX); DECLARE @dbschematable1 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N'1]'; DECLARE @dbschematable2 NVARCHAR(255) = N'[' + @DBName + N'].[' + @TableSchema + N'].[' + @TableName + N']'; --1. check the file, get column names, create table with these column names EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';'); SET @sql = 'SELECT TOP 0 * INTO ' + @dbschematable1 + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.16.0'', ''Text;Database=' + @PATH + ';HDR=YES;MaxScanRows=1;IMEX=1'', ''SELECT TOP 1 * FROM ' + @FILENAME + ''')'; EXEC (@sql); DROP TABLE IF EXISTS #colnames; CREATE TABLE #colnames (id INT, colname NVARCHAR(255)); EXEC ('INSERT #colnames SELECT column_id,name FROM ' + @DBName + '.sys.columns WHERE object_id = object_id(''' + @dbschematable1 + ''');'); SET @sql = 'CREATE TABLE ' + @dbschematable1 + '('; DECLARE @colname VARCHAR(MAX); DECLARE @colname2 VARCHAR(MAX); SELECT @colname = MIN(colname) FROM #colnames WHERE id = (SELECT MIN(id)FROM #colnames); WHILE @colname IS NOT NULL BEGIN SET @colname2 = @colname; IF @colname IN (SELECT currentname FROM #RenameThese) SELECT @colname2 = newname FROM #RenameThese WHERE currentname = @colname; SET @sql = @sql + '[' + @colname2 + '] [NVARCHAR](MAX) NULL,'; SELECT @colname = MIN(colname) FROM #colnames WHERE id = (SELECT MIN(id)FROM #colnames WHERE id > (SELECT c.id FROM #colnames c WHERE c.colname = @colname)); END; DROP TABLE IF EXISTS #colnames; SELECT @sql=TRIM(',' FROM @sql)+') ON [PRIMARY]'; EXEC ('DROP TABLE IF EXISTS ' + @dbschematable1 + ';'); EXEC (@sql); --now do the data insert EXEC ('BULK INSERT ' + @dbschematable1 + ' FROM ''' + @filepathandname + ''' WITH ( FORMAT = ''CSV'', FIELDQUOTE = ''"'', FIRSTROW = 2, FIELDTERMINATOR = '','', --CSV field delimiter ROWTERMINATOR = ''\n'', --Use to shift the control to next row TABLOCK )' ); IF @addExtraColumns = 1 BEGIN DECLARE @columnlisttable TABLE (collist VARCHAR(MAX)); INSERT @columnlisttable (collist) EXEC ('SELECT a.NAME FROM '+@DBName+'.sys.columns A WHERE a.object_id = OBJECT_ID(''' + @dbschematable1 + ''')'); SELECT collist FROM @columnlisttable SET @sql = 'DROP TABLE IF EXISTS ' + @dbschematable2 + '; SELECT * ' + IIF('FullName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(2440), NULL) AS FullName', '') + ' ' + IIF('LastName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS LastName', '') + ' ' + IIF('FirstName' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS FirstName', '') + ' ' + IIF('MiddleOtherNames' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(700), NULL) AS MiddleOtherNames', '') + ' ' + IIF('SuffixGen' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(NVARCHAR(70), NULL) AS SuffixGen', '') + ' ' + IIF('DOBDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DOBDT', '') + ' ' + IIF('DispositionDateDT' NOT IN (SELECT collist FROM @columnlisttable), ',CONVERT(DATE, NULL) AS DispositionDateDT', '') + ' INTO ' + @dbschematable2 + ' FROM ' + @dbschematable1 + ' DROP TABLE IF EXISTS ' + @dbschematable1; EXEC (@sql); END; ELSE BEGIN EXEC ('DROP TABLE IF EXISTS ' + @dbschematable2 + '; SELECT * INTO ' + @dbschematable2 + ' FROM ' + @dbschematable1 + ' DROP TABLE IF EXISTS ' + @dbschematable1); END; END; GO / comments
- Community
- SQL Prompt
- Format SQL fails when using TRIM() function
I am using the most recent version, 9.5.12.10845Here is the full script (Format SQL works if the line with TRIM() in it is commented out)USE [database1]GO/****** Object: StoredProcedure [dataimpor...
0 votes
