CODEWITHSUNDEEP
sqlsql-serverdatabase
user3012997
user3012997

Reputation: 15

Syntax Error but not sure why (SQL Server)

INSERT into LibraryTable(ID, Artist, Name, Rating, PlayCount) VALUES(
Select MIN(a.ID + 1)
From LibraryTable A
Left Join LibraryTable B
On A.ID = B.ID - 1
Where B.ID Is NULL, 'eg', 'eg', 1, 1)

im getting a syntax error at "Select" and the "," after null. im a beginner so any helpful tips would be greatly appreciated.

Upvotes: 0

Views: 47

Answers (3)

Christian Phillips
Christian Phillips

Reputation: 18749

You are trying to insert one value, but the syntax expects five. Try...

INSERT  INTO LibraryTable
        ( ID ,
          Artist ,
          Name ,
          Rating ,
          PlayCount
        )
        SELECT  MIN(a.ID + 1) AS ID ,
                'eg' ,
                'eg' ,
                1 ,
                1
        FROM    LibraryTable A
                LEFT JOIN LibraryTable B ON A.ID = B.ID - 1
        WHERE   B.ID IS NULL

There's an article here that explains.

Upvotes: 0

TotPeRo
TotPeRo

Reputation: 6781

TRY this:

INSERT into LibraryTable(ID, Artist, Name, Rating, PlayCount) 
Select MIN(a.ID + 1) as ID, 'eg' as Artist, 'eg' as Name, 1 as Rating, 1 as PlayCount
From LibraryTable A
Left Join LibraryTable B
On A.ID = B.ID - 1
Where B.ID Is NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want insert . . . select:

INSERT into LibraryTable(ID, Artist, Name, Rating, PlayCount)
    Select MIN(a.ID + 1), 'eq', 'eq', 1, 1
    From LibraryTable A Left Join
         LibraryTable B
         On A.ID = B.ID - 1
    Where B.ID Is NULL;

The values keyword is not used for this form of the insert.

If you are using SQL Server 2012, you can do this with the lead() function instead of a self join:

INSERT into LibraryTable(ID, Artist, Name, Rating, PlayCount)
    Select MIN(lt.ID + 1), 'eq', 'eq', 1, 1
    From (select lt.*, lead(id, 1) over (order by id) as nextid
          from LibraryTable lt
         ) lt Left Join
    where nextid <> id + 1;

Upvotes: 3

Related Questions