Why am I getting this error when trying to insert rows into my table?-CodePudding

I have a tabled called dbo.scannerCatalog which has these columns:
scannerID, scannerName, scannerUser, scannerStrength, countryID

Another table, dbo.scannerCoordinates, has these columns:
scannerID, lockCode, ZAxis, RAxis, CoordinateID

dbo.scannerCoordinates data looks like this:

scannerID	lockCode	ZAxis	RAxis	CoordinateID
1	akhr199	-43	95	238c9023-0e5c-4b63-b7f0-99861b0bf551
2	jtuw212	22	-20	2d2afe30-15fd-4168-8a55-b98a3b2dcd39
3	tyrh931	901	761	b93eaa24-038d-4d46-93d6-ac0c72305242
4	mriq762	-71	-9	a3326d0b-2cb3-48cd-82ac-59658fa5e9e8

I need to insert data from this query below, which shows rows in scannerCatalog that has a countryID of "gh3" that are NOT in scannerCoordinates:

SELECT * FROM scannerCatalog WHERE scannerID
NOT IN (    SELECT sc.scannerID FROM dbo.scannerCoordinates scanCoord
    LEFT JOIN scannerCatalog sc ON scanCoord.scannerID= sc.scannerID
    WHERE countryID = 'gh3')
AND countryID = 'gh3'

Is there a method to do an INSERT into dbo.scannerCoordinates using the data from the query above?

I tried this:

INSERT INTO dbo.scannerCoordinates
WHERE scannerID IN(
SELECT * FROM scannerCatalog WHERE scannerID
NOT IN (    SELECT sc.scannerID FROM dbo.scannerCoordinates scanCoord
    LEFT JOIN scannerCatalog sc ON scanCoord.scannerID= sc.scannerID
    WHERE countryID = 'gh3')
AND countryID = 'gh3'

But get this error:

Incorrect syntax near the keyword 'WHERE'.

CodePudding user response：

You are just missing the SELECT * FROM dbo.scannerCoordinates

Below should work.

INSERT INTO dbo.scannerCatalog
            SELECT  *
            FROM    dbo.scannerCatalog
            WHERE   scannerID NOT IN
                        (   SELECT      sc.scannerID
                            FROM        dbo.scannerCoordinates scanCoord
                            LEFT JOIN   scannerCatalog         sc ON
                                        scanCoord.scannerID = sc.scannerID
                            WHERE       countryID = 'gh3' )
                    AND countryID = 'gh3';