Member Avatar for Nebil

Hi,
I was wondering if there's a way to check an existing id in the main table when using temporary table,then use another id for inserting records.Since it may cause error for using the same id in the main table.
Is there a way to do that?

  • 2 Contributors
  • 7 Replies
  • 603 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Nebil

Dani AI

Generated

Short summary and practical fix.

As pointed out, the root cause is trying to insert values into a column the database is supposed to generate (AUTONUMBER / AUTO_INCREMENT / IDENTITY). Let the DB assign the primary key. The reliable pattern is: keep the identity column out of the temp-data you push back into the main table, or build your INSERT so it lists only the non-identity columns.

If you are building the insert in VB.NET from a DataTable, a safe approach is to generate a parameterized INSERT that excludes any DataColumn with AutoIncrement = True (or explicitly drop the ID column from the temp table). Example helper (adapt quoting to your RDBMS):

' Build an INSERT that only includes non-identity columns
Function BuildInsertSql(target As String, schema As DataTable) As String
    Dim cols As New List(Of String)()
    Dim pars As New List(Of String)()
    For Each c As DataColumn In schema.Columns
        If Not c.AutoIncrement Then
            cols.Add("`" & c.ColumnName & "`")
            pars.Add("@" & c.ColumnName)
        End If
    Next
    Return "INSERT INTO " & target & " (" & String.Join(", ", cols) & ") VALUES (" & String.Join(", ", pars) & ")"
End Function

' Usage: prepare the command once, then loop rows, add parameter values and ExecuteNonQuery().

Notes and cautions:

  • The MAX(id)+1 trick or copying the last row to "reserve" an id is brittle and not safe under concurrency; it also breaks when the main table is empty.
  • If you truly must preserve incoming IDs, use a mapping strategy or database-specific features (MySQL: INSERT IGNORE / ON DUPLICATE KEY UPDATE) but be careful about semantics.
  • Always use parameterized commands and wrap multi-row moves in a transaction to avoid partial failures.

Quick checklist: verify temp structure (no identity column), insert with explicit column list, handle constraints/nulls, and prefer letting the DB issue the primary keys.

Recommended Answers

All 7 Replies

Member Avatar for Reverend Jim

Try doing a SELECT COUNT(*) on the record with that ID. If you get a result of 0 then the record does not exist.

Member Avatar for Nebil

BTW does this work,the question i asked when a temp table is being created?And also if it does i kind of wrote this sql,can you comment on that?

"SELECT * INTO altempTable FROM [allowance]" & _
"WHERE [allowance number] NOT IN (SELECT * FROM [allowance] WHERE [allowance number]" & _
"IS NOT NULL"

"SELECT * INTO altempTable FROM [allowance] WHERE [allowance number] IS NULL"

The second statement is which i'm using currently doesn't give me the result that i want.Please help me on this one.

Edited by Nebil
Member Avatar for Reverend Jim

I don't believe the first query will do anything. The syntax is

WHERE [SOMEVALUE] NOT IN (LIST OF VALUES)

however your syntax is

WHERE [SOMEVALUE] NOT IN (RECORDSET)

What exactly are you trying to do? The second query should work but because I don't know what you are trying to do I can't say whether it will do what you want.

Member Avatar for Nebil

Here's the thing i have a primary key of type auto number in a main table.Before i add records to the main table i store them in a temp table.
Since the temp table is a copy of the main one and empty as well,new records that are stored in the temp table may hold a duplicate id if there exists a record in the main one.

And when you try to copy data to the main table it raises an exception for trying to insert a record with the same id.so first i want to check if there exists a record in the main table then if it exists,i will assign the next id to the temp table as it's created.Hence there's no problem with copying records from the temp table to the main one.

Member Avatar for Nebil

I kind of came up with this statement,what it does is it copies the last record to the temp table just to assign the next id to the new record and finally discrads it when copying the new records to the main table.But it only works if there's a record in the main table,any modification is absolutely appreciated.

qry1 = "SELECT * INTO pntempTable FROM per_diem_accomodation WHERE [pda number]=" & _
                "(SELECT MAX ([pda number]) FROM per_diem_accomodation)"



qry2 = "INSERT INTO per_diem_accomodation SELECT * FROM pntempTable WHERE [pda number]" & _
                    "NOT IN (SELECT MIN ([pda number]) FROM pntempTable)"
Edited by Nebil
Member Avatar for Reverend Jim

If a field has been designated as AUTONUMBER then you cannot include it in the insert query. If you want to insert all records from the temp table into the permanent table you must specify all the fields except the AUTONUMBER field in the select statement. For example, if I a table

table_1
ID          int (PK AUTO NUMBER)
field1      varchar(50)
field2      varchar(50)

I can create a temp table by

SELECT * INTO temp FROM table_1

but to add those records back into table_1 I must do

INSERT INTO table_1 SELECT field1, field2 FROM temp

Because you said the temp table is a copy (in structure) of the permanent table then the ID field is also AUTONUMBER and wiill not allow you to insert a record which has the ID field.

Edited by Reverend Jim
Member Avatar for Nebil

Thanks Jim,it worked.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.