Insert query - How I get the primary key - correct data format

Question

Hello,

I use Visual Studio 2017, C++, MFC and must add recordset inside a SQL database.

If I create an insert query, I need its primary key later?

How do I get this key, this new id, the best, the easiest way?

Are there templates for the date format for the SQL database. How does it have to look?

cdaorecordset-class

Answer 1

Ideally, you have the primary key with the data. This is one of the key features in a relational database: the key consists of one or more columns in the data. For instance, if you have table with cars, you would use the license plate or the VIN (Vehicle Identification Number) as the key. Likewise, in a table of persons, you would use the national registration number. Exactly how depends on which country you are in, and what persons you need to handle. (That is, you may need to handle both natural and juridical persons, or only one of these.)

Sometimes, though, it can be difficult to identify a key that is practically useful as such, and in this case you may have to introduce a surrogate key. But this is not a step to take lightly. Particularly, you will need to find a way to make sure that you to don't enter the same car, person etc twice.

Surrogate keys can be GUIDs, which you can send from the client. But it is common to have an integer value that is computed in the database. Typically you do this getting the current max value and generate new ones with the row_number function:

INSERT tbl(id, ...)
OUTPUT inserted.*
   SELECT @maxid + row_number() OVER(ORDER BY ...), col1, col2, col3
   FROM  @tvp

In this example I've added the OUTPUT clause which returns the rows inserted, so that you can get the ids. But you will need something in the input so that you can map the ids unambiguously.

Answer 2

The EANCode looks like something that might be the primary key. Or can there be more the one row per EANCode?

Again, this is the first thing to consider: do you need a surrogate key at all? If you don't need it, there is no reason to ask for how to retrieve it.

If you would need it, the OUTPUT clause will give back the inserted rows in a result set, just as if you had submitted a SELECT. If you don't know how to do that, you should learn to write data-access code for read-only operations first, so that you don't have to grasp too many things at a time.

The row_number function is an SQL function that numbers the rows in a result according to the ORDER BY clause you specify. I guess in this case, the order may not matter that much, but ORDER BY is mandatory with row_number.

You only need row_number if you are inserting multiple rows at the same time, but that is a common case, why I gave you an example where you pass the data in a table-valued parameter.

Now, I see that you have included a link to a DAO class. DAO is an API which is I don't know may years old, but it has to be at least 25. It has not been maintained on this side of year 2000 and lacks support for many modern features in SQL Server, including table-valued parameter. You should absolutely not use DAO for new applications. The preferred API for C++ code is ODBC, and you should use the ODBC Driver 17 for SQL Server, see here: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15.

I have not written ODBC applications in C++ myself, so I cannot assist with examples, but if you look at the TOC in the link I gave you, you will see that there is an entry for "ODBC Sample Application".

By the way, you may have other reasons for using C++, but it is easier to write data access code in C# with SqlClient. ODBC certainly has a higher learning curve.

Answer 3

Hi @Markus Freitag ,

SQL Server is a relational database, so it is necessary to establish a primary key for your table.

Please refer to the following method to create a primary key：

Create a primary key in an existing table
The following example creates a primary key on the column TransactionID in the AdventureWorks database.

    ALTER TABLE Production.TransactionHistoryArchive  
       ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);  

Create a primary key in a new table
The following example creates a table and defines a primary key on the column TransactionID in the AdventureWorks database.

    CREATE TABLE Production.TransactionHistoryArchive1  
       (  
          TransactionID int IDENTITY (1,1) NOT NULL  
          , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
       )  
    ;  

Or you can use surrogate keys.
A Surrogate Key can be implemented by an auto-incremented key. SQL Server supports an IDENTITY column to perform the auto-increment feature. It allows a unique number to be generated when a new record is inserted into the database table.

    --Syntax for Introducing Auto identity column with Create Table.      
    CREATE TABLE [dbo].[EmployeeMaster](      
    [EmployeeId] [int]IDENTITY(1,1) NOT NULL,      
    [EmployeeCode] [varchar](25) NULL,      
    [EmployeeName] [varchar](50) NULL,      
    [EmailAddress] [varchar](50) NULL,      
    )      
    --Syntax for Introducing Auto identity column with Create Table.      
    ALTER TABLE EmployeeMasterADD ID INT IDENTITY(1,1)  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.