Whats Wrong with This SQL Statement for Table Variable Bulk Insert

Whats wrong with this SQL statement for table variable bulk insert

You cannot BULK INSERT into a table variable. So this line:

BULK INSERT @TempTable 

Is what is causing the error.

---

FYI, the simplest fix for this is probably just to use a #Temp table instead of a Table Variable. So your SQL code would change to this:

CREATE TABLE #TempTable (FName nvarchar(max),SName nvarchar(max),
                          Email nvarchar(max));
BULK INSERT #TempTable 
FROM 'C:\52BB30AD694A62A03E.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

Need to BULK INSERT to a temporary table. It doesn't work with table variables, so how do I do it from within a function?

As already mentionned in the comment, the solution that differs the less to yours is doing that in a stored procedure rather than in a functoin, which is intended to modify the content of a table.

On a short term perspective, this should be clearly the easiest to implement for you but on a long term learnin SSIS could be a good investment.

Sql Server Bulk Insert into temporary table syntax error

It is not possible to bulk insert in table variable. so you can use temp table.

USE magdasync
    GO

CREATE Table #TempTable(
insz nvarchar(max),
firstname nvarchar(max),
middlename nvarchar(max),
lastname nvarchar(max),
birthdate date,
street nvarchar(max),
streetnumber nvarchar(max),
mailbox nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)
GO

BULK INSERT #TempTable
FROM 'C:\Workspaces\magdasync\src\main\examples\magdasync_input_example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO

Bulk Insert FROM a table variable

No, that's the way to do it. Definitely not RBAR, it should be pretty fast.

SQL Server - Is there any way to speed up inserts into a table variable in a TVF?

Given that the ultimate goal of storing results table variables was to perform isolated queries and join them in providing the output, the following approach using CTEs worked with the query overhead time the same (or maybe even faster) as the temporary table:

;WITH CTE1 as
(
    --Query 1 (took 6 seconds)
),
CTE2 as
(
    --Query 2 (took 6 seconds)
),
CTE3 as
(
    --Query 3 (took 8 seconds)
)
SELECT cte1.fielda, cte2.fieldb, cte3.fieldc, ...
FROM cte1
JOIN cte2 on cte1.fieldx = cte2.fieldx
JOIN cte3 on cte1.fieldx = cte3.fieldx

-- Total duration 20 seconds
-- Took 39 seconds previously

SQL Server bulk insert using variable

First you will need to split your data using XML Method & then you could Insert the Data in Table Variable by which you could update the Grade Table as New Marks available in Table Variable :-

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 

;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
UPDATE G
  SET
      G.Marks = M.marks
FROM grades G
     INNER JOIN @StudentsMark M ON G.PersonId = M.id;
SELECT *
FROM grades;

Result :

PersonId    Marks
1           0
2           1
3           2
4           5

Bulk INSERT Tbl From @path

Try with dynamic sql, something like this:

BEGIN
  declare @days as varchar(2) = '06'
  declare @path1 varchar(28) ='E:\WorkingTkr Data\_GDPD_02-'
  declare @path2 varchar(9) ='-2020.trk'
  declare @path3 varchar(40) = @path1+@days+@path2
          
  declare @sqlBulk varchar(max) = 
'bulk insert [dbo].[GDPD_Trk] ' + char(13) +
'from ''' + @path3 + '''' + char(13) + 
'with' + char(13) +
'(' + char(13) +
'  FIELDTERMINATOR = '' '',' + char(13) +  
'  ROWTERMINATOR = ''\n''' + char(13) +
')'
        
  exec (@sqlBulk)
END

If BULK INSERT fails because file does not exist then BULK INSERT Next file

I've applied the BEGIN TRY just to the EXEC variable , now it goes trough each of the existing 400 filenames, if it does not find 1 then it goes to the next file . If at the end only found 100 of the 400, the data of those 100 will be loaded into the table.

Once the script gets executed, it will show the error for all the files that were not found but it will load the ones found :)

                ;WHILE (SELECT COUNT(*)
                     FROM dbo.IBOFileNames_V1) > 0
            BEGIN
                    SET @FileName = ''
                    SET @FileName = (SELECT     TOP 1 FileName
                                       FROM     dbo.IBOFileNames_V1
                                       ORDER BY [Date] ASC)
                    SET @SQLScript = N' 
        BULK INSERT [dbo].[RawEventBCPIBO_V1]
        FROM ''' + @MainPath + @FolderPath + N'\' + @FileName + N''''

                    IF @FileType = 'TXT'
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                WITH
                (
                    FIRSTROW=1,
                    FIELDTERMINATOR=''\t'',
                    ROWTERMINATOR=''\n''
                ) '
                    END
                    ELSE
                    BEGIN
                            SET @SQLScript = @SQLScript + N' 
                    WITH
                    (
                        FORMAT=''CSV'',
                        FIRSTROW=1,
                        KEEPNULLS,
                        FIELDTERMINATOR='','',
                        ROWTERMINATOR=''\n''
                    ) '
                    END

                    BEGIN TRY
                    EXEC sp_executesql @SQLScript
                    END TRY

                    BEGIN CATCH
                        PRINT 'Error File - ' + @SQLScript;
                    END CATCH

                    
                    DELETE FROM dbo.IBOFileNames_V1
                    WHERE FileName = @FileName
            END

---

Related Topics