SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join 2 tables into a 3rd Empty Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/02/2013 :  19:48:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/02/2013 :  20:57:28  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/03/2013 :  01:20:11  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/03/2013 :  07:52:59  Show Profile  Reply with Quote
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 ????
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/03/2013 :  08:11:56  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/03/2013 :  09:30:02  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/03/2013 :  09:31:40  Show Profile  Reply with Quote
Or do I need to create the HBStatements table first?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/03/2013 :  09:36:42  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/03/2013 :  09:57:38  Show Profile  Reply with Quote
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
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/03/2013 :  10:01:57  Show Profile  Reply with Quote
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!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/03/2013 :  10:03:21  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/03/2013 10:04:46
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000