Create a table from a select statement

Good afternoon.
Who knows how I can easily create a table where the column names come from another table and are created dynamically?

That is to say. I need to create a new table where the columns, an unknown quantity, are created and given their names from an existing table.

Theory would say:

Create table Bin
as select xyz from ABC
group by xyz

Unfortunately, MS Query Analyzer complains about the AS !!
SQL 2000 server.

Look forward to hearing about the correct way of doing this :-)Yep, SQL Server doesn't like the Oracle format for doing this.

Try

Select XYZ
Into Bin
From ABC
Group by xyz

This will create the correct column data types & lengths but will not create any of their corresponding dependencies. If this is to be part of a process you will also need some sort of logic to determine if that table already exists.

Hope this helps.|||Thanks, good but this only works into a #temp, otherwise get "run sp_dboption" error.
This enters the data as rows. I need columns so that I can add the "real" data afterwards.
Any more ideas ?
Thanks very much|||Select into used to be discouraged because it was a nonlogged transaction that would invalidate your backup sequence. It may still be, though I couldn't find anything specific on this in Books Online.

Run this statement:

select DATABASEPROPERTY('YOURDBNAME', 'IsBulkCopy')

If the result is 0, your database is not set to allow non-logged transactions. You may need to change the setting to use SELECT INSERT.

blindman|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_1r94.asp

Is there a specific reason the SELECT INTO option is not permitted in the DB you are working on? You may want to look into it. As for creating just the columns, add the clause WHERE 1 = -1.|||Interesting, very interesting.
I shall have to look into this and find out why that DB has been set up in such a manner on Monday.
I will use a work round, export to .csv gives me a nice long, 1500 comma sperated names, and then just do a normal create and let it all work for a while :-)
What a nightmare, I hate work arounds, but it is a once off DB create.
Thanks for your help, I shall stay tuned and hope that I can be of help to you in the near future.
Take care and enjoy
HandyMac|||Option No 2
Open Fox 7, work with data, create the required table, import into SQL server.
Life is great :-)
Take care and have a great weekend,
HandyMacsql

Coverting mixed case data to UPPERCASE

I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.upper (COLUMNNAME) ?|||I tried your suggestion and it fixed my first issue, but now I have to figure out how to force the data to be uppercase whenever the user inserts/updates a client name. I would like to do this in a BEFORE trigger. Can anyone suggest a way for me to do that. Thanks.|||ahhhhh...smell the Oracle background...

nope INSTEAD of AND AFTER Triggers...

Have you thought about a constraint?|||User updating the column through the application or through QA ??
if application then
update table_name set column_name = upper(variable_here) will do

if you want a trigger
see instead of triggers in BOL ...

Counting things with SQL

I have a database with a table of basket names and a table of items in those
baskets.
I would like to find the size of the basket with the most items in.
I cannot figure out how to do this is T-SQL:-
nMax=0
for each basketName in basket
n=count(records in basketItem where
basket.basketName=basketItem.basketName)
if n>nMax then n=nMax
next
Is there a simple way of doing it, or would it be more efficient to retrieve
the size of each basket into an array in vb.net and figure out the largest
one there?
Andrew
Here is an example for the Northwind database, can be easely compared to
your database with the right key associated.
Select O.OrderID,Count(*) from Orders O
Inner join [Order Details] OD
On O.OrderID = OD.OrderID
Group by O.OrderIDa
Count All Orders Group by the OrderID
(Yeah newsgroupreaders, i know you can easely only count those OrderDetails,
but in this case the poster has the basketname in the 1 "Orders" Table of
the 1:n relationship)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Andrew Morton" <akm@.in-press.co.uk.invalid> schrieb im Newsbeitrag
news:uOMv$VcQFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a database with a table of basket names and a table of items in
>those baskets.
> I would like to find the size of the basket with the most items in.
> I cannot figure out how to do this is T-SQL:-
> nMax=0
> for each basketName in basket
> n=count(records in basketItem where
> basket.basketName=basketItem.basketName)
> if n>nMax then n=nMax
> next
> Is there a simple way of doing it, or would it be more efficient to
> retrieve the size of each basket into an array in vb.net and figure out
> the largest one there?
> Andrew
>
|||"Jens Smeyer" wrote
> Here is an example for the Northwind database, can be easely compared to
> your database with the right key associated.
> Select O.OrderID,Count(*) from Orders O
> Inner join [Order Details] OD
> On O.OrderID = OD.OrderID
> Group by O.OrderIDa
Thanks for that - is the next line meant to be included in the script
somehow?
Is it intended to modify the above query to return only the largest value?

> Count All Orders Group by the OrderID
[Also, is the Northwind database available to those of us who only have MSDE
to experiment with? So many Microsoft examples use it, but without it it can
be hard to follow the examples.]
Andrew
|||[vbcol=seagreen]
///
No this line was just for explaination.

> [Also, is the Northwind database available to those of us who only have
> MSDE to experiment with? So many Microsoft examples use it, but without it
> it can be hard to follow the examples.]
Of course, look here:
http://www.microsoft.com/downloads/d...displaylang=en
HTH, Jens Smeyer.
http://www.sqlserver2005.de
|||hi Andrew,
Andrew Morton wrote:[vbcol=seagreen]
> "Jens Smeyer" wrote
> Thanks for that - is the next line meant to be included in the script
> somehow?
> Is it intended to modify the above query to return only the largest
> value?
nope, this is a short description of what the query is performing..

> [Also, is the Northwind database available to those of us who only
> have MSDE to experiment with? So many Microsoft examples use it, but
> without it it can be hard to follow the examples.]
http://www.microsoft.com/downloads/d...DisplayLang=en
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>>> Count All Orders Group by the OrderID
> ///
> No this line was just for explaination.
Jens and Andrea,
That makes sense now - I was looking in the help at ALL and wondering how
that connected with the query, and "the" didn't get coloured when I typed it
into the stored procedure editor in VS.NET so I knew I was going wrong
somewhere!
And thank you for the link to the sample databases download. I couldn't find
it by searching just because of the sheer number of items found.
Best regards,
Andrew

Counting Rows

I have a simple report, listing patient names, and then columns depending on
certain condition. If they have the condition the column is True, and if
they don't the column is false.
The problem I have is trying to count how many have each condition. I
inserted the following expression in the footer:
=Count(cint(field!.condtion1.value)=-1)
If the column is true it does resolve to a -1, a 0 if it is false.
The expression just counts every row. It doesn't seem to evaluate the
expression.
Any help would be appreciated.
John HartJohn,
Try having the expression evaluate like this:
Count(iif(cint(field!.condtion1.value)=-1, 1, nothing))|||or try
SUM(iif(field!.condtion1.value = true, 1, nothing))