I have a table that i have already designed in SQL. My goal is to create a stored procedure to append data from a location into this table. It is a large data set (over 255 columns) so i cannot directly load this into MS access (front end) via VBA. I believe this only leaves with me an SQL option
I have scaled the internet for a solution to no avail.
basic code I tried below, however I am stuck and had no luck finding this.
INSERT INTO tablename (field 1, field 2) SELECT * FROM 'Z:tempCash_Activity_201910091702.csv';
So i figured it out and would like to post it out there for the world.
The issue with having so many columns and field with over 15 characters long is that the 15th character in a number is lost in excel.
Using VBA code, i imported the target file as a text file via VBA forcing the first column to be text. (i would recommend that you record the macro and click which columns you want as text)
Using VBA code, i then normalised the data the best way, axing unnecessary columns.
Once this was done, I created a Bulk Insert statement into my SQL Staging table and i called this using VBA code. File names are not handle well in SQL so i had to create an entire string just for the upload.
wb.ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;"mypath & LedgerString Destination:=Range( _ "$A$1"))
@filepath nvarchar(500) AS DECLARE @bulkinsert NVARCHAR(2000) SET @bulkinsert = 'BULK INSERT DBName.dbo.[table] FROM ''' + @filepath + ''' WITH ' + '( ' + 'FIRSTROW = 2, ' + 'FIELDTERMINATOR = '','', '+ 'ROWTERMINATOR = ''n''' + 'TABLOCK--, '+ ') ' + 'INSERT INTO table(FIELD NAMES) '+ 'SELECT DISTINCT NAMES '+ 'FROM table AS s ' + 'WHERE NOT EXISTS ( ' + 'SELECT * '+ 'FROM table As t '+ 'WHERE t.UniqueAuditID = s.UniqueAuditID '+ ') '+ 'delete from table '+ 'INSERT INTO NewTable(FIELD NAMES) '+ 'SELECT FIELD NAMES ' + 'FROM stagingtable'+ 'WHERE fileRef = ''' + @filepath + ''''