[vbnetskywalker]

I have two tables ........
I want to do something like this ... but it doesn't work however :

Insert Into T1 ( C1 ,C2) 
Values (
			 'C1' ,
			 (Select Max (C1) From T2)
		 )

I made a lot of combinations of sub queries , but none worked


and I'm avoiding to declare a var and put the (Max) in it , and then use it in the (Insert) statement

any way , I'm using VB.Net 2008 if it makes any difference


any help please
thanks in advace

This post has been edited by vbnetskywalker: 15 April 2009 - 11:41 PM

[Hary]

Insert Into T1 ( C1 ,C2)
Values (
             (Select 'C1' ,Max (C1) From T2) )

This should work. The result of the select is a table, and you cannot add a number and a table into C1,C2. Now it is a table (with one row), with two values which you can insert.

[vbnetskywalker]

thanks for fast replay

Not working sir
it gives me this msg

"Number of query values and destination fields are not the same"

i got the idea you mentioned of tables and numbers ,
and i tried some of combinations of mine , non worked either

what to do

[Hary]

What kind of SQL server are you using. MySQL only support subqueries from 4.1+

[vbnetskywalker]

oh , sorry didn't mention that I'm using MS Access 2003
is it bad ...... ?


but this worked

   insert into T1(C1)  Select  Max (C1) From T2

this means that access supports that (I've done it before)

[Hary]

No it is not, now i'm able to find its documentation:
http://msdn.microsof...y/bb208861.aspx

And solved it:

Insert Into T1 (C1 ,C2)  Select 'C1' , max(C1) From T2

[vbnetskywalker]

thanks man ........ now it works........

any way, this is not what my DB schema looks like .......
I'm goona try it on my DB ...... hope it works ......
thanks again ............

[kzimmerm]

Insert Into T1 ( C1 ,C2)
(Select 'C1', Max(C1) From T2)

This works also

Kurt

[Hary]

Yeah, and if you choose other variables names, or use capitals, that would work as well....

Writing it backwards won't work....

</ more useless tips>