how to enter the values to one table from two different tables in same database?

Question

1.00/5 (1 vote)

See more:

Hi, Friends I have Three Tables in my database, Table1 has 10 columns, Table2 Has 1 column, I Create the Table3 that contains 11 columns, How to insert Table1 values in Table3 first 10 columns and table 2 value in table3 last column.

SQL

insert into Table3(col1,col2,....,col11) VALUES SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2

I know this code is not correct, help me to correct it

Posted 18-Jun-15 20:35pm

Member 11724596

Add a Solution

Comments

RossMW 19-Jun-15 2:46am

Unless you have a link (or common reference field) between table2 and table1 then you can't align the data to the correct row. Or are you just trying to append the data one table after the other?

Member 11724596 19-Jun-15 2:55am

I want merge the two table datas in single table

RossMW 19-Jun-15 3:02am

Yes but are you expecting table3 to have table1 data followed by table2 data in completely seperate rows. i.e. If you just run two seperate insert statements

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)

OriginalGriff · Answer 1 · 2015-06-18T22:00:00

A table with one column can't be associated with data in a different table - unless it's a foreign key column, but then the foreign key data is already in the other table already! :laugh:

SQL rows aren't ordinal: SQL is at liberty to return rows in any order it finds convenient unless a specific ORDER BY clause is present in the query - so having two tables where the data has been entered in a specific order does not guarantee any ordering to the data when it is selected from the two tables - and the order you get with the same query is not necessarily going to be the same as the same query run a second later, if SQL has reorganised the data for any reason it feels it should.

So in practice, you can't do it - at least not in SQL - as you need a JOIN to "combine" the tables and you have no linking field.

As a one off, you could use C# or VB (for example) to SELECT all of the rows from each table in separate queries and then write a third query to combine them - or you could SELECT then into two temporary tables with a ROWCOUNT on each and use that at the linking field for the JOIN.

But either way, you are going to have to be very, very careful of exactly what data you end up with and check it very carefully to ensure your data integrity.

Me? I'd add a new row to the original table in the designer, and enter the "new data" from the second table again to make sure it goes to the right row each time.