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.

Tom H · Accepted Answer · 2010-07-22 14:56:03Z

2

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.

edited Jul 22, 2010 at 14:56

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

answered Feb 5, 2010 at 8:50

kevchadders

8,3454 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2010-02-05 08:26:31Z

0

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().

answered Feb 5, 2010 at 8:26

Guffa

703k111 gold badges760 silver badges1k bronze badges

Comments

LaustN · Accepted Answer · 2010-02-05 08:27:07Z

0

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.

answered Feb 5, 2010 at 8:27

LaustN

7203 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Insert Query (syntax question)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related