How to insert data from two tables into a single data without joins and set operators

Question

1.00/5 (2 votes)

See more:

I have two tables A (col1,col2) and B(col3,col4) with two columns in each table. I have third table C which i use to insert data from two tables A and B. If any one of the data does not contain data, data insertion to table c is getting failed. I have tried below method.

What I have tried:

insert into table c
(col1 , col2,col3,col4)
select t1.col1,t1.col2,t2.col3,t2.col3
from table A as t1, table b as t2

Posted 4-Jul-24 23:52pm

Anil kumar Adepu

Add a Solution

Comments

[no name] 5-Jul-24 6:13am

You obviously need to check first which tables contain the data that you are trying to copy.

3 solutions

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)

Pete O'Hanlon · Answer 1 · 2024-07-05T01:53:00

The only way you can possibly perform this is to read the data yourself programatically, and then issue the insert statements line by line. The pseudo-logic looks a bit like this:

Start
  Select the data from the master table
  Iterate over each row in the data
    Select the data from the secondary table where the id of the data item is the foreign key relationship in the secondary data
    Do a select against the table you are trying to insert into
      If the data is not present as indicated by the result of this select, insert it
  Continue Iteration
End

That, roughly, is how you would do it without set based operations, and without joins. Would I recommend this approach? No, I really wouldn't. I once saw a system that was developed just like this. In tests, the system took about 10 minutes to produce a report containing details of all invoices that had been paid, calculating the prices that had been allocated. That 10 minutes was on a dataset of 30 invoices. When the system went live, a nights run of data took 172 hours to run. My company was brought in to rewrite the system and we got the nights run down from longer than a week to 15 minutes and we did this by utilising what the database was really good at - set based operations.

OriginalGriff · Answer 2 · 2024-07-05T01:10:00

As Richard Deeming has said, what you are effectively doing a a cross join: the cartesian product of two tables, with all rows of table A mapped to all rows of table B - so if either table contains no rows, the result also contains no rows - 0 times any number is still 0!

Restricting solutions to "no joins" is pointless, since joins are what SQL is intended for - it's a Relational Database which depends on the relationship between tables to work, and that relationship is established in queries by joins.

You need to think about exactly what you expect table C to contain based on the relationship between tables A and B or all you can get is a massive pile of unrelated data dumped into a separate table - which is of no use to anyone!

Richard Deeming · Answer 3 · 2024-07-05T00:15:00

"Without joins and set operators" is a pointless artificial restriction. Unless this is some sort of homework assignment, arbitrarily ruling out an entire set of solutions to your problem serves absolutely no purpose.

Your code sample is using a join behind the scenes; you're just using an ancient and obsolete syntax for a CROSS JOIN. That is, since you haven't specified any conditions to relate the rows from the two tables, every row from table A will be matched to every row from table B, generating a cross-product of the two tables.

For example, if you have 10 rows in table A, and 100 rows in table B, you will end up with 1000 rows in table C.

If that is your intention, then you just need to update your syntax:

SQL

INSERT INTO TableC (col1, col2, col3, col4)
SELECT t1.col1, T1.col2, T2.col3, T2.col4
FROM tableA As t1 CROSS JOIN tableB As t2;

If your intention is not to produce a cross-product, then you need to specify the joining conditions to match rows from table A to rows from table B. For example:

SQL

INSERT INTO TableC (col1, col2, col3, col4)
SELECT t1.col1, T1.col2, T2.col3, T2.col4
FROM tableA As t1 FULL OUTER JOIN tableB As t2
ON t1.pk = t2.pk;

In the case of an outer join, you will end up with NULL values when there is no matching row. For example, given:

TableA      TableB
pk  col1    pk  col3
1   A       1   X
2   B       3   Y

you will end up with:

TableC
col1  col3
A     X
B     NULL
NULL  Y

Therefore, you need to ensure that the columns in table C allow NULL values.

Beyond that, you haven't explained what you mean by "getting failed". If you get an error, provide the full details of the error. If you're not getting the expected data, provide sample inputs, the expected output, and the actual output.

How to insert data from two tables into a single data without joins and set operators

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0