November 17, 2008 at 10:31 am
whats the correct way to write this i keep getting this error:
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
select
A.Account_number,
'PRF_CAN_ORDER_LEVEL' from dbo.AVW_11i_WA_BILLTO_Customers A UNION select 'PRF_CAN_OVERRIDE'
from dbo.AVW_11i_WA_BILLTO_Customers A
left join dbo.ContactPreferences C
on C.CustomerID = A.Account_number
November 17, 2008 at 10:57 am
As the error states, you'll need to have the same number of columns on both sides of the UNION. Your first query returns two columns, the second only returns one.
--SJT--
November 17, 2008 at 11:30 am
Or is this what you are trying to write?
select
A.Account_number as 'PRF_CAN_ORDER_LEVEL'
from
dbo.AVW_11i_WA_BILLTO_Customers A
UNION
select
A.Account_number as 'PRF_CAN_OVERRIDE'
from
dbo.AVW_11i_WA_BILLTO_Customers A
left join dbo.ContactPreferences C
on )C.CustomerID = A.Account_number );
November 17, 2008 at 12:23 pm
yes but what if i have 6 columns i need to insert into , how would i write this?
insert into col1,col2,col3,col4,col5,col6
col3 has 2 values = 'PRF_CAN_ORDER_LEVEL' , 'PRF_CAN_OVERRIDE' that need to be combined to 1 column
select A.Account_number, A.Account_number, 'PRF_CAN_ORDER_LEVEL' from dbo.AVW_11i_WA_BILLTO_Customers A UNION ALL select '1', '20', CONVERT(nvarchar(23), GETDATE(), 121), 'PRF_CAN_OVERRIDE'
from dbo.AVW_11i_WA_BILLTO_Customers A
left join dbo.ContactPreferences C
on C.CustomerID = A.Account_number
November 17, 2008 at 12:32 pm
1) Read the article whose link is listed below in my signature block.
2) Post everything the article tells you to post, CREATE TABLE statements for ALL TABLES/VIEWS involved(TESTED to be sure they work, and use base data types, not UDDT's), insert statements with sample data for ALL base tables, expected results, and MOST IMPORTANTLY the COMPLETE QUERY (or stored procedure, or update/insert/delete query) you are having problems with regarding this problem.
November 17, 2008 at 1:07 pm
can i have multiple insert statements ?
example
col1 = name
col1 =name_value
so my column should read as:
name
name_value
how would i write select and insert statement for this?
November 17, 2008 at 1:12 pm
Okay. I have no idea what you are trying to do or accomplish. The questions you are asking aren't making much sense, as they have little substance and no information to back them up.
You need to do 3 things. The first, get some training. The second, buy some books and read them. The third, invest $50.00 in SQL Server 2005 Developer Edition and install it on a computer at home and starting playing with it against the AdventureWorks sample database. Learn how to write select/insert/update/delete queries both single and multiple table varieties.
Be sure you take a backup of the database before you mess around with it so you can restore it to its original state whenever necessary.
November 17, 2008 at 1:16 pm
its just a general question? Can you have multiple inserts?
November 17, 2008 at 1:37 pm
Multiple inserts for what? Can you write:
create table #MyTest (
TestID int,
TestData varchar(10)
);
insert into #MyTest (TestID, TestData)
select 1, 'A';
insert into #MyTest (TestID, TestData)
select 2, 'B';
insert into #MyTest (TestID, TestData)
select 3, 'C';
select
TestID,
TestData
from
#Mytest;
drop table #MyTest;
Yes.
Again, what are you trying to accomplish?
Please tell me you aren't trying to learn SQL Server and T-SQL just by asking questions on these forums.
If so, get some books, get SQL Server 2005 Developer Edition, and start doing some self study first. All of this will make much more sense and you will remember things better if you do so.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply