Join 2 tables into a 3rd Empty Table

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

SQL Server 2008 Forums

Transact-SQL (2008)

Join 2 tables into a 3rd Empty Table

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-02 : 19:48:11

I would like to take 2 different tables called TransBalAfter and TransPreBal all the Columns from both and place them into a 3rd table called HBStatements.

Table 1
TransBalAfter Fields below
Reference
budgetpayment
Original-amt
Baldue
Seq-no
Cust-no
From-cust
Memo
Cust-po
date
Trans-code
Credit
Debit

Table 2
TransPrebalFields below
Cust-no
PreBalance
From-cust
Chargename
ChargeAdd
ChargeCity
ChargeSt
ChargeZip
Misc-code
SiteName
SiteAdd
SiteCity
SiteState
SiteZip

The 3rd Table I would like to have all the Column names from the 2 tables above. I know some of the names are in both tables but they are not duplicate records so example cust-no in table 1 and also in table 2 are 2 different records but they should be 2 rows but cust-no are in the same place. Please help thank you!!!!

Cust-no
123456
123456

Not
Cust-no Cust-no
123456
123456

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-02 : 20:57:28

Do you mean something like this?

select
	a.Reference,
	a.budgepayment,
	--- and other columns in Table1
	b.PreBalance,
	b.[From-Cust]
	--, and other columns in Table2
into 
	HBStatements
from
	TransBalAfter  a
	inner join TransPreBal  b on a.[Cust-No] = b.[Cust-No]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 01:20:11

sounds like this to me


select
Cust-no, other columns...
from TransBalAfter 

union all

select
Cust-no, other columns... in same order as above
FROM TransPrebal

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 07:52:59

Visakh16 yes the union is correct but then how do i get both of those into HBStatements. Also I am guessing The columns that are not in the first table must be added and same for table 2. See example below. How do I take this below and insert it into HBStatements.

select
[Cust-no],[From-cust],budgetpayment,[Original-amt],Baldue,[Seq-no],Memo,[Cust-po],date,[Trans-code],Credit,Debit,Reference,
NULL AS Chargename, NULL AS ChargeAdd,NULL AS ChargeCity,NULL AS ChargeSt,NULL AS ChargeZip,NULL AS [Misc-code],NULL AS SiteName,NULL AS SiteAdd,NULL AS SiteCity,NULL AS SiteState,NULL AS SiteZip, NULL AS PreBalance
from TransBalAfter

UNION ALL

select
[Cust-no],[From-cust],NULL AS budgetpayment,NULL AS [Original-amt],NULL AS Baldue,NULL AS [Seq-no],NULL AS Memo,NULL AS [Cust-po],NULL AS date,NULL AS [Trans-code],NULL AS Credit,NULL AS Debit,NULL AS Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,PreBalance
FROM TransPrebal

??? into HBStatements ????

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-03 : 08:11:56

Any column that does not exist in other table, yes, use "Null as ColumnName"
And following is the insert sample

INSERT INTO HBStatements (Cust-no, all other columns in same sequence as that are in below select statement)
SELECT Columns From Table1
Union
SELECT columns From Table2

Cheers
MIK

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 09:30:02

MIK by doing it your way it gives me an error saying Invalid object Name 'HBStatements'.. This is a table that is not created yet I was hoping that it would be created once I imported the data.

INSERT INTO HBStatements ([Cust-no],[From-cust],budgetpayment,[Original-amt],Baldue,[Seq-no],Memo,[Cust-po],date,[Trans-code],Credit,Debit,Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,PreBalance)

select
[Cust-no],[From-cust],budgetpayment,[Original-amt],Baldue,[Seq-no],Memo,[Cust-po],date,[Trans-code],Credit,Debit,Reference,
NULL AS Chargename, NULL AS ChargeAdd,NULL AS ChargeCity,NULL AS ChargeSt,NULL AS ChargeZip,NULL AS [Misc-code],NULL AS SiteName,NULL AS SiteAdd,NULL AS SiteCity,NULL AS SiteState,NULL AS SiteZip, NULL AS PreBalance
from TransBalAfter

UNION ALL

select
[Cust-no],[From-cust],NULL AS budgetpayment,NULL AS [Original-amt],NULL AS Baldue,NULL AS [Seq-no],NULL AS Memo,NULL AS [Cust-po],NULL AS date,NULL AS [Trans-code],NULL AS Credit,NULL AS Debit,NULL AS Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,PreBalance
FROM TransPrebal

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 09:31:40

Or do I need to create the HBStatements table first?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-03 : 09:36:42

offcouse if Insertion is required .. then there should be a table into which you will need to insert the data.
If you want to keep HBStatements be a product facing table, then you can create any other table e.g. xyz, into which you export this data then perform whatever operation/testing you want and once finalize ..move correct data from xyz to HBStatements.

Cheers
MIK

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 09:57:38

ok new Error after I fixed it comes up with String or Binary Data would be truncated.

INSERT INTO HBStatements ([Cust-no],[From-cust],PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,Memo)

select
[Cust-no],[From-cust],NULL AS PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,
NULL AS Chargename, NULL AS ChargeAdd,NULL AS ChargeCity,NULL AS ChargeSt,NULL AS ChargeZip,NULL AS [Misc-code],NULL AS SiteName,NULL AS SiteAdd,NULL AS SiteCity,NULL AS SiteState,NULL AS SiteZip,Memo
from TransBalAfter

UNION ALL

select
[Cust-no],[From-cust],PreBalance,Null AS Credit,Null AS Debit,NULL AS budgetpayment,NULL AS [Original-amt],NULL AS Baldue,NULL AS [Seq-no],NULL AS [Cust-po],NULL AS date,NULL AS [Trans-code],NULL AS Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,Null AS Memo
FROM TransPrebal

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-03 : 10:01:57

Fixed I changed some of the fields to become larger Valchar(250) to even larger. The above Insert I posted works perfectly now. Thank you all!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-03 : 10:03:21

you have a column's datatype length in HBStatements table, smaller than the data length coming through sql queries.
It happens when you try to insert 'MIK' into a column having Char(2) or Varchar(2) datatype. since the length of 'MIK' is 3 and the destination column can only store 2 characters.

Find out the problematic column and increase its length. hope that clarifies.

Glad that you fixed it

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources