invalid object name error - 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

invalid object name error

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 01:31:01

Im trying to create a new table from a union all statement, im pretty sure this is the way you do it:

insert into Test_table
select * from Tb1
union all
select * from Tb2

However im receiving a invalid object name error. Doing a search on this forum i read it might be to do with not having tb1 or tb2 in the same database, but both select statements and the union work, just not the insert or creating a new table from the results. Any suggestions will be greatful.
Champinco

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-03-20 : 01:38:06

May be your Test_Table does not exists...
If Test_Table doesn't exists do the following...
select * into Test_Table from Tb1
union all
select * from Tb2

MohammedU

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 01:47:22

Yes you are correct Test_Table does not exist.
I require the results of the union to be inserted or create a new table.
i.e: the result of:
select * from Tb1
union all
select * from Tb2

to be inserted into a new table.
Champinco

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 18:19:08

Any suggestions on how to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-20 : 18:28:25

INSERT INTO doesn't create the table for you, so that's why you are getting the error. SELECT INTO does create it for you.

SELECT *
INTO Test_Table
FROM
(
select * from Tb1
union all
select * from Tb2
) t

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 19:03:26

Cheers. that will work.
Also how do i append two separate queries together? they have different column names but I fixed that up by putting dummy columns in the query that had less/more. I thought it was union all, and as i understand it, a union does the same except it looks for duplicates. all i want to do is append two queries together, with same column names.
Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-20 : 19:10:45

I don't understand what you mean. Please show us an example.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 19:20:18

Sorry it worked.
Union all is like an Append, it will add my two datasets/queries together.
Sorry...On the initial issue though i cant get that to work. I.e: the result of my union all to be inserted into a new table. here is my complete query. I would like to insert this result into a new table.


SELECT     	PERIOD.STARTDATE,
		PERIOD.DESCRIPTION,
		COMMISSION_TXN.PERIODSEQ, 
		COMMISSION_TXN.SUPER_DISTRIBUTOR_CODE, 
                COMMISSION_TXN.PREMISE_CODE, 
		COMMISSION_TXN.DISTRIBUTOR_CODE, 
		COMMISSION_TXN.INTERNAL_EXTERNAL, 
		COMMISSION_TXN.SERVICE_ID, 
		cast(COMMISSION_TXN.ACCOUNT_NUMBER as varchar), 
		cast(COMMISSION_TXN.PROMOTION_CODE as varchar), 
		COMMISSION_TXN.PRODUCTDESCRIPTION, 
		COMMISSION_TXN.PRODUCTID, 
		COMMISSION_TXN.PROMOTION_NAME, 
		COMMISSION_TXN.EVENTTYPEID, 
		COMMISSION_TXN.MEASUREMENT_NAME,
		Mobile_revenue = 'null',
		COMMISSION_TXN.COMMISSION_AMOUNT,
		Prod_id = 'null'
FROM         COMMISSION_TXN inner JOIN
                      PERIOD ON COMMISSION_TXN.PERIODSEQ = PERIOD.PERIODSEQ
union all
SELECT distinct 
		PERIOD.STARTDATE, 
		PERIOD.DESCRIPTION,
		TC_Mobile_Usage.PERIODSEQ,
		Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE1 AS Dealer_premise_code,  
		Dealer_Hierarchy.DISTRIBUTOR_CODE,
		internal_external = ('External'),
		TC_Mobile_Usage.GENERICATTRIBUTE2 AS Service_Id, 
		account_number = ('none for txn file'),
		promotion_code = ('Usage'),
		Product_description = ('Usage'),
		Product_id = ('Usage'),
		promotion_name = ('Usage'),
		TC_Mobile_Usage.EVENTTYPEID, 
		measurement_name = ('Usage'),
		sum(TC_Mobile_Usage.VT_VALUE) AS Mobile_revenue,
		sum(TC_Mobile_Usage.CONTRIBUTIONVALUE) AS Commission_Amount, 
		TC_Mobile_Usage.PRODUCTID as Prod_ID
FROM         TC_Mobile_Usage INNER JOIN
                      PERIOD ON TC_Mobile_Usage.PERIODSEQ = PERIOD.PERIODSEQ INNER JOIN
                      Dealer_Hierarchy ON TC_Mobile_Usage.GENERICATTRIBUTE1 = Dealer_Hierarchy.PARTICIPANTID
WHERE     (NOT (TC_Mobile_Usage.PRODUCTID = 'iddv')) AND (NOT (Dealer_Hierarchy.POSITIONTYPEID LIKE 'MI %')) AND 
                      (TC_Mobile_Usage.PERIODSEQ IN (106))--, 107, 108, 110, 111, 112, 114))
group by 	PERIOD.STARTDATE, 
		PERIOD.DESCRIPTION,
		TC_Mobile_Usage.PERIODSEQ,
		Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE1,
		Dealer_Hierarchy.DISTRIBUTOR_CODE,
		TC_Mobile_Usage.GENERICATTRIBUTE2,
		TC_Mobile_Usage.EVENTTYPEID, 
		TC_Mobile_Usage.PRODUCTID

tziker, said to do this i should do the following:


Select *
into test_usage_commission_txn
from (
QUERY ABOVE
)

but that doesnt seem to work. I want to create a new table from the result of my union query as above. how can this be done?

Cheers
Champinco

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-20 : 19:31:13

You are missing the derived table alias, which I named t.

SELECT *
INTO Test_Table
FROM
(
YourQueryGoesHere
) t

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 19:43:17

perfect...i knew something was wrong. Cheers
The error it was giving me was throwing me off.
Champinco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources