0

Run T-SQL script below:

DECLARE @FileLocation varchar(8000);
SET @FileLocation='C:\Temp';
DECLARE @count as int=1;
DECLARE @maxCount int;
DECLARE @JSON varchar(max);
DECLARE @FileName varchar(1000);

CREATE TABLE #temp(
    ID int,
    ReadingTime datetime,
    DataSequenceNumber int
)

--CHECK FOR TEMP TABLE
IF OBJECT_ID('tempFileTable') IS NOT NULL
    BEGIN
        DROP TABLE tempFileTable;
    END

IF OBJECT_ID('tempFileTable') IS NULL
    BEGIN
        --SAVE the result into temptable to get the details of the filename
        CREATE TABLE tempFileTable
        (
            FileID [int] IDENTITY(1,1) NOT NULL,
            Filename varchar(max),
            FileDepth int,
            FileNumber int
        )
        INSERT INTO tempFileTable
        EXEC xp_dirtree @FileLocation, 2, 1

        SET @maxCount=@@ROWCOUNT
    END

WHILE(@count<=@maxCount)
BEGIN
    SET @FileName= CONCAT(@FileLocation,'\',(SELECT filename FROM tempFileTable WHERE FileID=@count))
    SELECT @FileName;

    DECLARE @SQL varchar(MAX);
    IF OBJECT_ID('tempBulkTable') IS NOT NULL
        DROP TABLE tempBulkTable

    SET @SQL = 'SELECT * INTO tempBulkTable FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_CLOB) AS import';
    EXEC (@SQL);

    SELECT @JSON=BulkColumn FROM tempBulkTable; 

    INSERT INTO #temp
    SELECT *
    FROM OPENJSON (@JSON)
    **WITH** (
        ID int,
        ReadingTime datetime,
        DataSequenceNumber int
    )

    SET @count=@count+1;
END

-- Insert all the data accumulated in #temp to meterlog table
INSERT INTO meterlog(
    ID, ReadingTime, DataSequenceNumber
)
SELECT 
    t.ID, t.ReadingTime, t.DataSequenceNumber 
FROM #temp t
INNER JOIN Details m ON t.ID=m.ID
AND m.[No] = (SELECT ISNULL(MAX([No]), 0) FROM [dbo].Details WHERE ID = m.ID)

-- Update Increments in meterlog table
UPDATE t1 
SET 
    ......
FROM [dbo].[Meters] AS t1 
LEFT JOIN [dbo].[Log] AS t2 ON t1.[ID] = t2.[ID] 
    AND t1.[No] = t2.[No] 
WHERE t1.[Readingtime] >= (SELECT MIN(CAST(CONVERT(CHAR(17), [ReadingTime], 113) AS DATETIME)) FROM #temp); 

DROP TABLE #temp;

Get an error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Added semicolon in front of WITH:

INSERT INTO #temp_meterlog
    SELECT *
    FROM OPENJSON (@JSON)
    ;WITH **(**

Then I add another error:

Incorrect syntax near '('.

I am confused with the code. Can anyone point potential issues around 'WITH' in the script? And what should I modify to the code?

4
  • 1
    Firstly, this won't fix your problem, but you should be terminating your statements with ; not just adding one in front of the WITH. Commented Jun 14, 2024 at 1:17
  • Secondly you were at the wrong reference documents, the WITH that complains about a ; is a CTE WITH not an OPENJSON with. Commented Jun 14, 2024 at 1:19
  • 4
    Are you sure you are using a version of SQL Server which supports OPENJSON? I just ran your code locally and it worked fine. Commented Jun 14, 2024 at 1:21
  • 5
    What does the following return? SELECT compatibility_level, @@VERSION FROM sys.databases WHERE database_id = DB_ID() Commented Jun 14, 2024 at 2:32

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.