SQL for dummies
Moderator: druellan
SQL for dummies
Is anyone willing to write a concise worked example, aimed at non-programmers, of how to query ZXDB using Heidi SQL?
A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?
Does one need to write an .SQL script & run it in Heidi, or can you actually click things in the Heidi package to do it?
A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?
Does one need to write an .SQL script & run it in Heidi, or can you actually click things in the Heidi package to do it?
Re: SQL for dummies
Any particular reason that you would like to use HeidiSQL and not something else as SQL client in your tutorial?
By the way, the difficult moment for non-programmer could be installing the database locally itself.
I went through it, here's my old post about it. I used SqlYog and not Heidi as my tool:
viewtopic.php?f=32&t=328&p=4052&hilit=i ... zxdb#p4044
By the way, the difficult moment for non-programmer could be installing the database locally itself.
I went through it, here's my old post about it. I used SqlYog and not Heidi as my tool:
viewtopic.php?f=32&t=328&p=4052&hilit=i ... zxdb#p4044
Re: SQL for dummies
Heidi SQL is what I already have for testing the spreadsheet generated SQL that I then send to Einar.
I'm exposing my ignorance here, but this is my current level: I download the latest ZXDB from the GitHub. I open HeidiSQL and run the SQL file that populates it in Heidi. Have I 'installed it'?
For example, if I click on 'entries' and 'data', I'm able to see all the game entries. I assume there is a way of analysing what I already can see.
I can read and understand the SQL code that inserts new entries into the DB, and occasionally make small modifications. But I can't write my own SQL code. I assumed if a query is done as an SQL statement, if someone posted a written example, I'd be able to modify it for my own needs.
But looking at the thread you link to, I can see that I'm totally out of my depth!
I'm exposing my ignorance here, but this is my current level: I download the latest ZXDB from the GitHub. I open HeidiSQL and run the SQL file that populates it in Heidi. Have I 'installed it'?
For example, if I click on 'entries' and 'data', I'm able to see all the game entries. I assume there is a way of analysing what I already can see.
I can read and understand the SQL code that inserts new entries into the DB, and occasionally make small modifications. But I can't write my own SQL code. I assumed if a query is done as an SQL statement, if someone posted a written example, I'd be able to modify it for my own needs.
But looking at the thread you link to, I can see that I'm totally out of my depth!
Re: SQL for dummies
Looks like somehow you have already a working SQL server, so ignore my post about installing it.
I guess, I could help with some basic SQL queries. Give me some time if nobody else is volunteering.
I guess, I could help with some basic SQL queries. Give me some time if nobody else is volunteering.
Re: SQL for dummies
If you had time to demonstrate a simple example it would be great Ralf. Basically if someone could show how to answer this question:
or even something simpler, that would probably be enough for me to find my own way with it.A useful example would be "how many games are there beginning with "J", post 1993, and are MODs?
Re: SQL for dummies
Try this:
SELECT releases.release_year, entries.*
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%' AND releases.release_year >= 1993 AND entries.is_mod = 1
That'll give you all the records matching. To get the count:
SELECT COUNT(*) as cnt
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%'
AND releases.release_year >= 1993
AND entries.is_mod = 1
That's specific to MS SQL Server - I think in this case the MySQL syntax will be the same.
SELECT releases.release_year, entries.*
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%' AND releases.release_year >= 1993 AND entries.is_mod = 1
That'll give you all the records matching. To get the count:
SELECT COUNT(*) as cnt
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%'
AND releases.release_year >= 1993
AND entries.is_mod = 1
That's specific to MS SQL Server - I think in this case the MySQL syntax will be the same.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
All SQL clients will provide the same result, it doesn't matter that you are using Heidi SQL.
All games:
Code: Select all
SELECT * FROM entries
Code: Select all
SELECT * FROM entries
WHERE library_title like 'J%'
Code: Select all
SELECT * FROM entries
WHERE upper(library_title) like 'J%'
Code: Select all
SELECT * FROM entries
WHERE upper(library_title) like 'J%'
AND id in (SELECT entry_id FROM releases WHERE release_year > 1993)
Code: Select all
SELECT * FROM entries
WHERE upper(library_title) like 'J%'
AND id in (SELECT entry_id FROM releases WHERE release_year > 1993)
AND id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')
Re: SQL for dummies
Actually I believe ZXDB would benefit from placing there some basic sql views showing in practice joins between tables and eliminating the need to write more complex queries each time.
[mention]Einar Saukas[/mention], what do you think about it?
[mention]Einar Saukas[/mention], what do you think about it?
Re: SQL for dummies
Good idea Ralf, but I think [mention]Einar Saukas[/mention] probably has enough on his plate. I'm happy to publish the queries used on the SC website.
Re: SQL for dummies
[mention]R-Tape[/mention]
I would probably start queries looking at one table only, probably entries. Just with select field name serrated by commas, then from entries. Just as described in the past from Einar. Don't run before you can walk.
Then start looking at inner joins, so using more then one table, then finally move onto outer joins.
I'm always happy to help too.
I would probably start queries looking at one table only, probably entries. Just with select field name serrated by commas, then from entries. Just as described in the past from Einar. Don't run before you can walk.
Then start looking at inner joins, so using more then one table, then finally move onto outer joins.
I'm always happy to help too.
Re: SQL for dummies
Thanks fellas. Einar's post worked a treat. It's given me a starting point to experiment with; it all seemed quite impenetrable before.
Re: SQL for dummies
Great stuff, the world is your oyster now.
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
All games beginning with J (case-insensitive), originally released after 1993, that are mods:
Code: Select all
SELECT * FROM entries
WHERE upper(library_title) like 'J%'
AND id in (SELECT entry_id FROM releases WHERE release_seq = 0 AND release_year > 1993)
AND id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
Yes, it's the same syntax. Your query works perfectly, except column entries.is_mod doesn't exist anymore in latest ZXDB versions. There's now a new table relations that stores all information about games related to other games. A certain game is considered a "mod" if this game ID is stored in this table, marked as "mod" of another game. Technically it means replacing this:Vampyre wrote: ↑Mon Nov 18, 2019 3:16 pm Try this:
SELECT releases.release_year, entries.*
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%' AND releases.release_year >= 1993 AND entries.is_mod = 1
That'll give you all the records matching. To get the count:
SELECT COUNT(*) as cnt
FROM entries INNER JOIN releases ON entries.id = releases.entry_id
WHERE title like 'J%'
AND releases.release_year >= 1993
AND entries.is_mod = 1
That's specific to MS SQL Server - I think in this case the MySQL syntax will be the same.
Code: Select all
SELECT * FROM entries
WHERE is_mod = 1
Code: Select all
SELECT * FROM entries
WHERE id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
Sure! Here's my first attempt:
Code: Select all
SELECT e.id, e.title, e.library_title, e.is_xrated, e.is_crap, mt.text AS machinetype,
e.max_players, gt.text AS genretype, st.text AS spot_genretype, pt.text AS publicationtype,
vt.text AS availabletype, e.known_errors, e.comments, e.spot_comments, e.hardware_blurb,
e.without_load_screen, e.without_inlay, e.hide_from_stp, i.text AS idiom, e.mag_ratings,
e.issue_id, e.book_isbn, e.book_pages,
GROUP_CONCAT(IF(a.team_id IS NULL,la.name,CONCAT(la.name,' [',lt.name,']')) ORDER BY a.author_seq SEPARATOR ', ') AS authors,
r0.release_year AS original_release_year, r0.release_month AS original_release_month,
r0.release_day AS original_release_day, r0.budget_price AS original_budget_price,
r0.microdrive_price AS original_microdrive_price, r0.disk_price AS original_disk_price,
r0.cartridge_price AS original_cartridge_price,
GROUP_CONCAT(l0.name ORDER BY p0.publisher_seq SEPARATOR ', ') AS original_publishers,
GROUP_CONCAT(CONCAT(ea.title,' (',ea.id,')') ORDER BY ea.library_title SEPARATOR ', ') AS authored_with,
GROUP_CONCAT(CONCAT(ed.title,' (',ed.id,')') ORDER BY ed.library_title SEPARATOR ', ') AS pack_depending_on,
GROUP_CONCAT(CONCAT(ee.title,' (',ee.id,')') ORDER BY ee.library_title SEPARATOR ', ') AS editor_of,
GROUP_CONCAT(CONCAT(ei.title,' (',ei.id,')') ORDER BY ei.library_title SEPARATOR ', ') AS inspired_by,
GROUP_CONCAT(CONCAT(em.title,' (',em.id,')') ORDER BY em.library_title SEPARATOR ', ') AS mod_from,
GROUP_CONCAT(CONCAT(ep.title,' (',ep.id,')') ORDER BY ep.library_title SEPARATOR ', ') AS another_platform_for,
GROUP_CONCAT(CONCAT(er.title,' (',er.id,')') ORDER BY er.library_title SEPARATOR ', ') AS runs_with,
GROUP_CONCAT(CONCAT(eu.title,' (',eu.id,')') ORDER BY eu.library_title SEPARATOR ', ') AS upgraded_from
FROM entries e
INNER JOIN releases r0 ON r0.entry_id = e.id AND r0.release_seq = 0
LEFT JOIN authors a ON a.entry_id = e.id
LEFT JOIN labels la ON la.id = a.label_id
LEFT JOIN labels lt ON lt.id = a.team_id
LEFT JOIN publishers p0 ON p0.entry_id = r0.entry_id AND p0.release_seq = r0.release_seq
LEFT JOIN labels l0 ON l0.id = p0.label_id
LEFT JOIN machinetypes mt ON e.machinetype_id = mt.id
LEFT JOIN genretypes gt ON e.genretype_id = gt.id
LEFT JOIN genretypes st ON e.spot_genretype_id = st.id
LEFT JOIN publicationtypes pt ON e.publicationtype_id = pt.id
LEFT JOIN availabletypes vt ON e.availabletype_id = vt.id
LEFT JOIN idioms i ON i.id = e.idiom_id
LEFT JOIN relations ra ON ra.entry_id = e.id AND ra.relationtype_id = 'a'
LEFT JOIN entries ea ON ra.original_id = ea.id
LEFT JOIN relations rd ON rd.entry_id = e.id AND rd.relationtype_id = 'd'
LEFT JOIN entries ed ON rd.original_id = ed.id
LEFT JOIN relations re ON re.entry_id = e.id AND re.relationtype_id = 'e'
LEFT JOIN entries ee ON re.original_id = ee.id
LEFT JOIN relations ri ON ri.entry_id = e.id AND ri.relationtype_id = 'i'
LEFT JOIN entries ei ON ri.original_id = ei.id
LEFT JOIN relations rm ON rm.entry_id = e.id AND rm.relationtype_id = 'm'
LEFT JOIN entries em ON rm.original_id = em.id
LEFT JOIN relations rp ON rp.entry_id = e.id AND rp.relationtype_id = 'p'
LEFT JOIN entries ep ON rp.original_id = ep.id
LEFT JOIN relations rr ON rr.entry_id = e.id AND rr.relationtype_id = 'r'
LEFT JOIN entries er ON rr.original_id = er.id
LEFT JOIN relations ru ON ru.entry_id = e.id AND ru.relationtype_id = 'u'
LEFT JOIN entries eu ON ru.original_id = eu.id
GROUP BY e.id
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
Ops! My first attempt had a bug that duplicated some data. Here's my second attempt:
Code: Select all
CREATE VIEW z_products AS
SELECT e.id, e.title, e.library_title, e.is_xrated, e.is_crap, mt.text AS machinetype,
e.max_players, gt.text AS genretype, st.text AS spot_genretype, pt.text AS publicationtype,
vt.text AS availabletype, e.known_errors, e.comments, e.spot_comments, e.hardware_blurb,
e.without_load_screen, e.without_inlay, e.hide_from_stp, i.text AS idiom, e.mag_ratings,
e.issue_id, e.book_isbn, e.book_pages,
GROUP_CONCAT(DISTINCT CONCAT(la.name,IF(a.team_id IS NULL,'',CONCAT(' [',lt.name,']')),IF(la.id in (select label_id from roles where entry_id = e.id),
(SELECT CONCAT(' (',GROUP_CONCAT(tx.text ORDER BY tx.text SEPARATOR ', '),')') FROM roles rx INNER JOIN roletypes tx ON rx.roletype_id = tx.id
WHERE rx.entry_id = a.entry_id and rx.label_id = a.label_id GROUP BY rx.entry_id, rx.label_id),'')) ORDER BY a.author_seq SEPARATOR ', ') AS authors,
r0.release_year AS original_release_year, r0.release_month AS original_release_month,
r0.release_day AS original_release_day, r0.budget_price AS original_budget_price,
r0.microdrive_price AS original_microdrive_price, r0.disk_price AS original_disk_price,
r0.cartridge_price AS original_cartridge_price,
GROUP_CONCAT(DISTINCT l0.name ORDER BY p0.publisher_seq SEPARATOR ', ') AS original_publishers,
GROUP_CONCAT(DISTINCT concat(sa.title,' (',si.text,')') ORDER BY sa.library_title SEPARATOR ', ') AS original_aliases,
GROUP_CONCAT(DISTINCT CONCAT(ea.title,' (',ea.id,')') ORDER BY ea.library_title SEPARATOR ', ') AS authored_with,
GROUP_CONCAT(DISTINCT CONCAT(ed.title,' (',ed.id,')') ORDER BY ed.library_title SEPARATOR ', ') AS pack_depending_on,
GROUP_CONCAT(DISTINCT CONCAT(ee.title,' (',ee.id,')') ORDER BY ee.library_title SEPARATOR ', ') AS editor_of,
GROUP_CONCAT(DISTINCT CONCAT(ei.title,' (',ei.id,')') ORDER BY ei.library_title SEPARATOR ', ') AS inspired_by,
GROUP_CONCAT(DISTINCT CONCAT(em.title,' (',em.id,')') ORDER BY em.library_title SEPARATOR ', ') AS mod_from,
GROUP_CONCAT(DISTINCT CONCAT(ep.title,' (',ep.id,')') ORDER BY ep.library_title SEPARATOR ', ') AS another_platform_for,
GROUP_CONCAT(DISTINCT CONCAT(er.title,' (',er.id,')') ORDER BY er.library_title SEPARATOR ', ') AS runs_with,
GROUP_CONCAT(DISTINCT CONCAT(eu.title,' (',eu.id,')') ORDER BY eu.library_title SEPARATOR ', ') AS upgraded_from,
GROUP_CONCAT(DISTINCT xc.name ORDER BY xc.name SEPARATOR ', ') AS competitions,
GROUP_CONCAT(DISTINCT xf.name ORDER BY xf.name SEPARATOR ', ') AS features,
GROUP_CONCAT(DISTINCT xj.name ORDER BY xj.name SEPARATOR ', ') AS control_options,
GROUP_CONCAT(DISTINCT xl.name ORDER BY xl.name SEPARATOR ', ') AS prog_languages,
GROUP_CONCAT(DISTINCT xn.name ORDER BY xn.name SEPARATOR ', ') AS turn_modes,
GROUP_CONCAT(DISTINCT CONCAT(xs.name,' (#',xs.series_seq,')') ORDER BY xs.name SEPARATOR ', ') AS series,
GROUP_CONCAT(DISTINCT xt.name ORDER BY xt.name SEPARATOR ', ') AS themes,
GROUP_CONCAT(DISTINCT xu.name ORDER BY xu.name SEPARATOR ', ') AS unsorted_sets,
GROUP_CONCAT(DISTINCT xy.name ORDER BY xy.name SEPARATOR ', ') AS multiplayer_modes
FROM entries e
INNER JOIN releases r0 ON r0.entry_id = e.id AND r0.release_seq = 0
LEFT JOIN authors a ON a.entry_id = e.id
LEFT JOIN labels la ON la.id = a.label_id
LEFT JOIN labels lt ON lt.id = a.team_id
LEFT JOIN publishers p0 ON p0.entry_id = r0.entry_id AND p0.release_seq = r0.release_seq
LEFT JOIN labels l0 ON l0.id = p0.label_id
LEFT JOIN machinetypes mt ON e.machinetype_id = mt.id
LEFT JOIN genretypes gt ON e.genretype_id = gt.id
LEFT JOIN genretypes st ON e.spot_genretype_id = st.id
LEFT JOIN publicationtypes pt ON e.publicationtype_id = pt.id
LEFT JOIN availabletypes vt ON e.availabletype_id = vt.id
LEFT JOIN idioms i ON i.id = e.idiom_id
LEFT JOIN relations ra ON ra.entry_id = e.id AND ra.relationtype_id = 'a'
LEFT JOIN entries ea ON ra.original_id = ea.id
LEFT JOIN relations rd ON rd.entry_id = e.id AND rd.relationtype_id = 'd'
LEFT JOIN entries ed ON rd.original_id = ed.id
LEFT JOIN relations re ON re.entry_id = e.id AND re.relationtype_id = 'e'
LEFT JOIN entries ee ON re.original_id = ee.id
LEFT JOIN relations ri ON ri.entry_id = e.id AND ri.relationtype_id = 'i'
LEFT JOIN entries ei ON ri.original_id = ei.id
LEFT JOIN relations rm ON rm.entry_id = e.id AND rm.relationtype_id = 'm'
LEFT JOIN entries em ON rm.original_id = em.id
LEFT JOIN relations rp ON rp.entry_id = e.id AND rp.relationtype_id = 'p'
LEFT JOIN entries ep ON rp.original_id = ep.id
LEFT JOIN relations rr ON rr.entry_id = e.id AND rr.relationtype_id = 'r'
LEFT JOIN entries er ON rr.original_id = er.id
LEFT JOIN relations ru ON ru.entry_id = e.id AND ru.relationtype_id = 'u'
LEFT JOIN entries eu ON ru.original_id = eu.id
LEFT JOIN aliases sa ON sa.entry_id = e.id AND sa.release_seq = 0
LEFT JOIN idioms si ON si.id = sa.idiom_id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'C') AS xc ON xc.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'F') AS xf ON xf.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'J') AS xj ON xj.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'L') AS xl ON xl.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'N') AS xn ON xn.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id, m.series_seq FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'S') AS xs ON xs.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'T') AS xt ON xt.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'U') AS xu ON xu.entry_id = e.id
LEFT JOIN (SELECT g.name, m.entry_id FROM members m INNER JOIN groups g ON m.group_id = g.id AND g.grouptype_id = 'Y') AS xy ON xy.entry_id = e.id
GROUP BY e.id
Re: SQL for dummies
Good to know, thanks. It's about time I upgraded my local ZXDB to a more recent version!Einar Saukas wrote: ↑Tue Nov 19, 2019 1:21 pm
Yes, it's the same syntax. Your query works perfectly, except column entries.is_mod doesn't exist anymore in latest ZXDB versions. There's now a new table relations that stores all information about games related to other games. A certain game is considered a "mod" if this game ID is stored in this table, marked as "mod" of another game. Technically it means replacing this:
with this:Code: Select all
SELECT * FROM entries WHERE is_mod = 1
This change allows Ku-Ku to be stored as "mod" of 2 different games, for instance.Code: Select all
SELECT * FROM entries WHERE id in (SELECT entry_id FROM relations WHERE relationtype_id = 'm')
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
Re: SQL for dummies
Does anyone know why HeidiSQL doesn't display all information for long lists?
In this case I want to see the max id in magrefs, and when I click 'show all' (which works for other tables), I can only see as far as 120,732. When I query with "SELECT MAX(id) FROM magrefs;", it reports the true max of 222,344.
Just a quirk of the software?
In this case I want to see the max id in magrefs, and when I click 'show all' (which works for other tables), I can only see as far as 120,732. When I query with "SELECT MAX(id) FROM magrefs;", it reports the true max of 222,344.
Just a quirk of the software?
Re: SQL for dummies
Hi [mention]R-Tape[/mention],
Is there anything in Preferences > Text Formatting > Number of rows displayed in data tab ?
Is there anything in Preferences > Text Formatting > Number of rows displayed in data tab ?
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
The log window at the bottom should have a message like this:R-Tape wrote: ↑Mon Jul 06, 2020 2:18 pm Does anyone know why HeidiSQL doesn't display all information for long lists?
In this case I want to see the max id in magrefs, and when I click 'show all' (which works for other tables), I can only see as far as 120,732. When I query with "SELECT MAX(id) FROM magrefs;", it reports the true max of 222,344.
Just a quirk of the software?
Code: Select all
/* Browsing is currently limited to a maximum of 100,000 rows. To see more rows, increase this maximum in Tools > Preferences > Data. */
Re: SQL for dummies
Yep that was the issue. Thanks fellas. The title of this thread definitely applies here!
Re: SQL for dummies
I have this working SQL code for new magrefs:
What is the easiest way to make the code choose the next id up? Magrefs are not grouped by property, so I think it should just be max(id)+1.
I tried this but it didn't work:
I also modified some of Einar's SQL that works for labels (which I don't understand), but it didn't work either.
Any suggestions?
Code: Select all
insert into magrefs(id, referencetype_id, entry_id, label_id, topic_id, issue_id, page, is_supplement, score) values(222362, 0, 36228, NULL, NULL, 612, 19, 0, NULL);
I tried this but it didn't work:
Code: Select all
...values(select max(id)+1 from magrefs...
Code: Select all
(select k from (select max(id)+1 as k from magrefs) as x)
Re: SQL for dummies
It would need extra brackets round the subquery
Values((select max(Id)+1), ...
Assuming it's not an identity column
Values((select max(Id)+1), ...
Assuming it's not an identity column
Re: SQL for dummies
Tried that. I get the error: Table 'magrefs' is specified twice, both as a target for 'INSERT' and as a separate source for data. Does that mean it's an identity column, and if so, how to handle this?
- Einar Saukas
- Bugaboo
- Posts: 3213
- Joined: Wed Nov 15, 2017 2:48 pm
Re: SQL for dummies
You can simply omit ID since it's an identity column:
Code: Select all
insert into magrefs(referencetype_id, entry_id, label_id, topic_id, issue_id, page, is_supplement, score) values(0, 36228, NULL, NULL, 612, 19, 0, NULL);