combining two tables as one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

combining two tables as one

Author

Topic

dirac
Starting Member

12 Posts

Posted - 2007-06-09 : 11:15:47

is it possible to combine two tables(not related with each other) as onde like we put them together with our hands physically.

this is what i want;here's the two tables to be combined:

table-a ______________table-b
15 _ a ____________ ny _____ arena
25 _ d ____________ fg_____ metus
35 _ f ____________
45 _ f ____________
these two tables above will become table-c like below;

table-c
15__arena
25__metus
35__null
45__null
but a warning , no relation between the tables and, row counts will not be equal anytime one of them may have more rows then the other ,ihe tried many join methods but gave me allways the lots of results more than i want ,please anyone can help? is it possible to put two tables physically like we put them together with our hands ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-09 : 11:27:13

use UNION ALL


select col1, col2 from tablea
union all
select col1, col2 from tableb

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-09 : 12:13:31

union won't work unless the datatypes in the columns you are unioning are the same or implicitly convertible. looks like you have strings in the first column of table b, so you'll have to convert col1 of tablea to strings too:

select convert(varchar(10),col1), col2 from tablea
union all
select col1, col2 from tableb

elsasoft.org

dirac
Starting Member

12 Posts

Posted - 2007-06-09 : 12:44:29

first of all , tahnk you for the interest but union alligns the two tables data vertically, i want them as they are, :) it's so simply to take one tabel left then bring the other one from the right side then combine as they were but when it comes to making it with sql looks like impossible :( [ so simple to say one table on the left and the other table on the right then make them one but in practically , couldn't do that :(]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-09 : 13:38:06

if you want to join them that way, you need a column to join on. what is the common key between the two tables?

elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-09 : 19:23:24

seems no relation between the 2 table. What you want looks like just randomly join one row of tablea to tableb ?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-10 : 09:43:25

perhaps he wants the cartesian product then...

select * from tablea inner join tableb on 1=1

elsasoft.org

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-06-10 : 16:01:01

Go through this, hope it will fulfill your requirements.
Replace TABLE1, TABLE2 and its column as per your table structure.

-- INSERTING INTO TEMP TABLE FROM TABLE1
CREATE TABLE #DEMO (
COL1 INT IDENTITY(1,1) NOT NULL,
COL2 VARCHAR(50)
)

INSERT INTO #DEMO
SELECT TABLE1.COL3 -- ANY COLUMN OF TABLE1
FROM TABLE1

-- INSERTING INTO TEMP TABLE FROM TABLE2
CREATE TABLE #DEMO1(
COL1 INT IDENTITY(1,1) NOT NULL,
COL2 VARCHAR(50)
)
INSERT INTO #DEMO1
SELECT TABLE2.COL4 -- ANY COLUMN OF TABLE2
FROM TABLE2

-- CREATING RIGHT JOIN BETWEEN THE TWO TEMP TABLES.
SELECT #DEMO.COL2, #DEMO1.COL2
FROM
#DEMO LEFT JOIN #DEMO1
ON #DEMO.COL1 = #DEMO1.COL1 -- JOIN THE TEMP TABLES ON IDENTITY COLS

DROP TABLE #DEMO
DROP TABLE #DEMO1

"Life is not a bed of roses."

dirac
Starting Member

12 Posts

Posted - 2007-06-11 : 04:23:20

thanks for your answers guys , youre all great ;)
but i got that many complex things can be done by the sql commands but the simplest thing which is putting two tables side by side is impossible(cause no relationship and we don't know when or which one's id count is more than the other one) :(

anyway ,you gave me lots of wonderful ideas thanks to all repliers :)

by the way for those who may be interested, i found a solution(but a forced one);

i am planning to put one insert trigger for the table-a in order to insert to another table(table-c)and one insert trigger for the table-b in order to update the last record by the table two then tatttaaa :) this is the only solution i could find .

and a message from me to microsoft please put a command like this: " just select table-a.col.1,table-a.col2,table-b.col1 and don't touch anything " ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 08:26:58

<<
and a message from me to microsoft please put a command like this: " just select table-a.col.1,table-a.col2,table-b.col1 and don't touch anything " ;)
>>

So you dont want RDBMS

Left outer join with related key is the way to go

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-11 : 08:42:46

dirac - it sounds like you should be using microsoft Excel, not a relational database.

- Jeff
http://weblogs.sqlteam.com/JeffS

dirac
Starting Member

12 Posts

Posted - 2007-06-11 : 09:35:52

no mr. jsmith8858 i am using sql server 2005 express but the reason that the tables are not related is this : the first table is holding the income receipts of the corp. and the second table is holding the payments for that receipts , but the first or the second table MAY have "partial" payments so the row count differs on these two tables time to time and because of this i couldn't use a primary key and the result no primary key no relation :( thanks again to all.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-11 : 09:48:32

You are not correctly modeling your data ... It is hard to know for sure based on your vague description, but all you need is something like this:

create table Receipts (ReceiptID int primary key, .... more columns ...)
create table Payments (PaymentID int primary key, ReceiptID references Receipts(ReceiptID), ... more columns ...)

this allows you to relate payments to receipts, and you can have as many payments as you want for each receipt; it is simply two tables with a simple relation between the two.

If you provide more specific details about the data you need to track, we can help further to give you a simple, solid relational model. There is a reason SQL won't let you do what you are trying to do with your existing data -- it is not a good design! In general, when this happens, you shouldn't be thinking "SQL needs to change to accommodate what I want!", you should be thinking "what do I need to change in my data model so that it works with SQL?" Doesn't the latter make more sense?

I strongly recommend that you read up on the basics of data modeling and normalization if it is not too late to put together a good, solid relational design for your application. Otherwise, it will become a big mess and you'll need hack upon hack to access your data when instead you could just use simple SQL statements ...

- Jeff
http://weblogs.sqlteam.com/JeffS

fsabery
Starting Member

2 Posts

Posted - 2012-04-23 : 04:41:06

Hi,
You can use two fill outer join on two table where each table has e Row_Number like the following:
Select * from
( select Row_Number( ) over( order by <a column from table1>) rNum, * from <table1> ) as tbl1
fill outer join
( select Row_Number( ) over( order by <a column from table2>) rNum, * from <table1> ) as tbl2
on ( tbl1.[rNum] = tbl2.[rNum] )

fsabery
Starting Member

2 Posts

Posted - 2012-04-23 : 04:43:18

Hi,
sorry my last post had some mistakes,
You can use fill outer join on two tables where each table has a Row_Number like the following:
Select * from
( select Row_Number( ) over( order by <a column from table1>) rNum, * from <table1> ) as tbl1
fill outer join
( select Row_Number( ) over( order by <a column from table2>) rNum, * from <table2> ) as tbl2
on ( tbl1.[rNum] = tbl2.[rNum] )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources