0

Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a subroutine to insert all of this data into an Access database. What I'm struggling with is working out how to use SQL to INSERT all of the data.

There are 20 tables, and the main table is called Entry, which all the other tables link back to. To give a simplified example of the data structure, there is also a Keyword table, which links back to the Entry. And there is the Keyword_Priority table, which links back to Keyword. Both relationships are one-to-many, as below:

enter image description here

My question

I can use SQL INSERT for the Entry table to create one record. But how would I then create a record for the Keyword table, referring to the primary key of the Entry record I've just created? And how would I then create a record for Keyword_Priority, referring to the Keyword record? And so on.

This is all going to be done through VB.NET - I can't use Forms because there are 180,000+ entries to input. Is there any way to store a variable in SQL that I can refer to later?

3
  • 1
    Use INSERT .. SELECT with WHERE condition by a table which was previously inserted in using data values which were inserted.
    – Akina
    Commented Dec 2, 2019 at 6:20
  • Since you mentioned 180,000+ entries is your data source in text files tab delimited or csv files?
    – user168186
    Commented Dec 2, 2019 at 6:41
  • @Kumar - Neither, the data source is an XML file. That's why it needed to be parsed first before being inputted into a database.
    – Lou
    Commented Dec 2, 2019 at 10:30

2 Answers 2

1

you can also do it like here https://stackoverflow.com/questions/4135437/get-last-insert-id-from-access

by making use of @@identity

select @@IDENTITY

which will return the latest created ID for the database connection

OR make use of the "inbound" property that most id-columns are simply growing int values - so

select max(id) from Entry
6
  • Oh sweet. So if I were to perform two sequential inserts, the second one just needs select @@IDENTITY to grab the ID from the first for the foreign key value?
    – Lou
    Commented Dec 2, 2019 at 10:32
  • 1
    yep - but I suggest you read up on the MSDN article : learn.microsoft.com/en-us/previous-versions/dotnet/…
    – eagle275
    Commented Dec 2, 2019 at 10:45
  • So I've tried the following statement: "INSERT INTO Keyword (Keyword, EntryFK) VALUES (?, (SELECT @@IDENTITY FROM Entry))" But it throws an exception "Query input must contain at least one table or query". Any idea where this has gone wrong?
    – Lou
    Commented Dec 2, 2019 at 14:06
  • And I've tried deleting the FROM clause so it's just SELECT @@IDENTITY within the VALUES, but that doesn't work either. This time it just says that there's a Syntax error but doesn't specify.
    – Lou
    Commented Dec 2, 2019 at 14:09
  • did you read up on the whole procedure in the linked MSDN document?
    – eagle275
    Commented Dec 2, 2019 at 14:15
1
INSERT INTO Entry (EntrySequence) 
VALUES ('new EntrySequence');

INSERT INTO Keyword (Keyword, EntryFK)
SELECT 'new Keyword', Entry.EntryID
FROM Entry
WHERE Entry.EntrySequence = 'new EntrySequence';

INSERT INTO Keyword_Priority (KeywordPriority, KeywordFK)
SELECT 'new KeywordPriority', Keyword.Id
FROM Keyword /* , Entry */
WHERE Keyword.Keyword = 'new Keyword'
/* AND Entry.EntrySequence = 'new EntrySequence'
   AND Entry.EntryID = Keyword.EntryFK */
1
  • Brilliant. Simple and looks like it should work great. Will try both yours and @eagle275's approaches and see what works. Thanks!
    – Lou
    Commented Dec 2, 2019 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.