SQL Insert Query (syntax question)

Question

I have an sql query which is working, it does insert the correct data, but the ID's are out of synch:

 Dim sql As String = ("INSERT INTO [CANRADcontacts]") & _
        (" ([Title], [Initials], [FirstName], [LastName], [Organisation], [Department], [Address], [Code],") & _
        (" [Telephone], [WorkTelephone], [Fax], [CellularTelephone],[Email], [ResearchFocus], [CollID], [ResearchFunders])") & _
        (" VALUES (@Title, @Initials, @FirstName, @LastName, @Organisation, @Department, @Address, @Code, ") & _
        ("@Telephone, @WorkTelephone, @Fax, @CellularTelephone, @Email, @ResearchFocus, @CollID, @ResearchFunders); ") & _
        ("INSERT INTO [CANRADcollreg] ([CollID], [FlightDateArrive], [FlightNameArrive], [FlightTimeArrive], [FlightDateDepart], [FlightNameDepart], [FlightTimeDepart], [AttendingLaunch], [Accomodation], [DietaryRequirements], [SpecificDietaryRequirement]) ") & _
        ("VALUES (@CollID, @FlightDateArrive, @FlightNameArrive, @FlightTimeArrive, @FlightDateDepart, @FlightNameDepart, @FlightTimeDepart, @AttendingLaunch, @Accomodation, @DietaryRequirements, @SpecificDietaryRequirement)")

In the database I have two columns (ContactsID on CANRADcollreg and ID on CANRADcontacts) which I need to synch up and have the same ID inserted when a record is inserted. Please can you assist on how I would go about achieving this?

Thanks and regards.

Answer 1

If you want to use an automatically created id (auto_increment / identity) from one query in another query, there is usually a function available to get that.

In SQL Server for example you use the function scope_identity() to get the identity created in the previous query.

In Access you use @identity. In MySQL you use last_insert_id().

Answer 2

Is the ID of CANRADcontacts marked as an identifier? If so, have a look at the documentation for @@identity.

This should give you the value of the most recently created ID (at least in Transact-SQL, possibly also for other systems).

For this to work, you do ofc need to do the insert on CANRADcontacts before the insert on CANRADcollreg.

Answer 3

Before you do your two Inserts I would get the MAX value of ContactID and add 1 to it. I would then store this in a temp field, which could be used in both INSERT INTO statements.

You would have to include something like this at the start, or do this as a separate step just before you run these inserts.

Dim sql As String = ("DECLARE @ContactID AS INT" ) & _
  (" SET @ContactID = ((SELECT MAX(ContactID) FROM CANRADcontacts) + 1) & _ 
(" INSERT INTO [CANRADcontacts]") & _ 
(" ([ContactID], [Title], [Initials], [FirstName], [LastName]...
... etc
(" VALUES (@ContactID, @Title, @Initials, @FirstName, @LastName,

You would then have to include your ContactID in the INSERT INTO command. Of course if your using an IDENTITY for you ContactID, then this method wont work.