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?
;not just adding one in front of theWITH.WITHthat complains about a;is a CTEWITHnot anOPENJSONwith.OPENJSON? I just ran your code locally and it worked fine.SELECT compatibility_level, @@VERSION FROM sys.databases WHERE database_id = DB_ID()