How to Formulate Insert SQL Query for ADO Usage

Question

2.60/5 (2 votes)

See more:

Hello everyone!

I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.

I have 2 tables, Table1 and Table2, and I need to insert some data into Table2, but also I have to insert value of Table1's primary key which is autonumber.

I have tried to insert Table1's primary key with this query:

SQL

INSERT INTO Table2
(
  table1_pk
)
SELECT
  MAX(Table1.table1_pk)
FROM Table1;

It worked.

I have tried to insert some data into Table2 with this query:

SQL

INSERT INTO Table2
(
  some_column
)
VALUES
(
  some_value
)

It worked.

My question is, how can I insert into Table2 both Table1's primary key value and some data?

For example:

SQL

INSERT INTO Table2
(
  table1_pk,
  some_column
)
VALUES
(
  SELECT
    MAX(Table1.table1_pk)
  FROM Table1,
  some_data
);

I know the above query is incorrect; it just serves to illustrate my question.

I work in MS Visual Studio Express 2008, on Windows XP, in C++, using pure WIN32 API.

If any other information is required (source code or something similar), please ask for it; I will more than gladly supply it.

Posted 21-Jun-13 12:20pm

MyOldAccount

Updated 21-Jun-13 13:23pm

AspDotNetDev

v3

Add a Solution

Comments

AspDotNetDev 21-Jun-13 19:25pm

This isn't really your question, but you should generally avoid using MAX to get the last created primary key, as that may not always be correct. Use @@IDENTITY instead: http://msdn.microsoft.com/en-us/library/ms187342.aspx

MyOldAccount 22-Jun-13 2:10am

I have tried using "select @@identity from SOME_TABLE", and it worked.

However, my SQL query triggers when user presses button on a GUI, and after I press button more than once, information message pops out saying that @@identity can be used only once.
If you know a solution to this problem, please help.
Thank you.

AspDotNetDev 22-Jun-13 2:34am

I have no idea what you are doing. Please update your question to explain in more detail. Or create a new question if you have a different question.

MyOldAccount 15-Jul-13 17:45pm

Sorry for late reply, I was busy, here is what I need to do:

I have a button that saves data into database when pressed.
In that button handler, this is what I try to do:
First I add data to main table-primary key ( which is AUTONUMBER ) and other stuff.
Then, I need to add that table's primary key to child table, since that primary key is a foreign key in the child table.

That's the issue:
SQL allows to insert data OR primary key, separately, yet I need to do it in one command.

To clarify things with small example:

When user clicks on a button, first insert data for main table.
Then insert data for child table.

That's it.

But I don't know how to insert BOTH foreign key and other relevant data into child table.

Again, a small snippet:

case IDC_BUTTON_SAVE:
{
insert into Table1( ... ) values( ... );

// the problem part

insert into ChildTable( foreign key, ... ) values( Table1.primary key, ... );
}

AspDotNetDev 15-Jul-13 17:54pm

So, you need to insert a row into Table1, and one of the fields is Table1.AutoGeneratedPrimaryKey. You need to insert the value of AutoGeneratedPrimaryKey into Table2.ForeignKeyToTable1AutoGeneratedPrimaryKey?

First, ensure that the field Table1.AutoGeneratedPrimaryKey is set to be an identity field (so it will create a number for you automatically). Then, ensure your INSERT statement is actually a compound of two statements, with the second statement being a select statement that returns SCOPE_IDENTITY(). If you are using a newer version of SQL Server, you can use the OUTPUT clause to return the ID without creating a compound statement. Then, use ExecuteScalar() to execute the statement and get the ID.

Once you have the ID, you can pass that as a parameter to another query that inserts into Table2.

See here for a full explanation: http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id

MyOldAccount 15-Jul-13 18:24pm

I use MS Access...
Does that make any difference?

AspDotNetDev 15-Jul-13 18:28pm

Perhaps. I would give it a try and see if it works.

MyOldAccount 15-Jul-13 18:31pm

OK, thank you.

I will try, and post results as soon as I can.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bilashcse · Answer 1 · 2013-06-21T13:27:00

Solution 1

This article will be helpful for you. Link : http://www.cprogramming.com/tutorial/c++_database_access_using_ado.html[^]

Posted 21-Jun-13 13:27pm

Bilashcse