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.
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;
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
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.
VALUESdoes not work withSELECTin that way, just doINSERT INTO table(field, ...) SELECT...