Field Names from SQL Statement

Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?

For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK

resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME

Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.

Just a shot in the dark because I don't know if anyone has already done something like this before.

Thank you in advance,

JeffI'm not quite sure what you are after... but, assuming that all columns are aliased... it would be the 'word' preceding the commas and the FROM.

Where are you trying to consume this from? ADO.NET will expose these field names to you, or are you looking to somehow consume them in just SQL Server?

--Mike|||What I am trying to do is build a report editor whereby the user simply enters a SQL statement on one tab. The second tab would then contain a datagrid with the field names as one column and some other columns that are formatting the report such as column width, forecolor, etc. for each field. All of this information is then written to a database table to be used later to restore that report definitions which are used to build a dynamic datagrid to display the results of the query.

Unfortunately, not all fields are aliased so I cannot simply use those words that precede a comma. Additionally, there are also times when fields are concatenated and include commas such as TABLE1.LAST_NAME + ', ' + TABLE1.FIRST_NAME AS FULL_NAME.

Fetching duplicate rows uisng IN clause

I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith

Fast loading relational data

I am searching for a way to fast load relation data. I know how to load data fast but how can i store relation data fast.
For example :
Table1 ( tabel1Id int identity , name varchar(255) )
Table2 ( tabel2Id int identity , table2Id , name varchar(255))
When i insert 50 records into Table1 i can't get the 50 identity fields back, to insert the related data into Table2.
I think one of the solutions could be returning a selection of Table1 joined with syslockinfo, but i have no idea how to do it.
Does anyone have an idea?

Add uniqueidentifier column to your master table.

When inserting, generate the value by newid() function and insert it alongside your 50 records. Then you can easily find all the records inserted.

|||Thank you for your reply.
I also thought of that. But i also thought that it must be possible to join with syslockinfo.
|||

I would suggest that you correlate the values in the insert. So, say you have to insert an invoice and line items.

invoiceNumber
000000333
000000334

invoiceNumber product amount
000000333 widget 10
000000334 wadget 11
then the invoice insert is easy, and the line item insert is something like:

insert into invoiceLineItem (invoiceId, product, amount)
select invoiceId, product, amount
from invoice
join (select '000000333' as invoiceNumber,'widget' as product,10 as amount
union all
select '000000334','wadget',11) as newInvoiceLines
on invoice.invoiceNumber = newInvoiceLines.invoiceNumber

Of course exactly how you get the bold stuff will depend on the format of your data (like a spreadsheet, or from a user interface, XML, etc. In reality I would have expected you to have to get a productId as well.