MS SQL Server :: Variable For The Table Name In A SELECT Statement.

Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?

SET NOCOUNT ON

Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end

Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'

select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase

SET NOCOUNT OFF

View Replies !

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)

SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')

SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)

PRINT @VALUE_KEEP

PRINT @COLUMN_NAME

RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME

SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent

How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View Replies !

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy

Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View Replies !

Variable In A Select Statement
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.

select @column_name from table

View Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter.

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

View Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30)

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

Thanks,
Judith

View Replies !

SQL Select Statement With A 'string' Variable?
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);

View Replies !

Using A Variable For Tablename In Select Statement?
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View Replies !

Variable Not Holding Value For Select Statement
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID ))) but for some reason it will not work with the next select statement...

can someone make the pain go away and help me here..??

erik..

GOSET ANSI_NULLS ON GO
ALTER PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
WHERE ' + @GenericColumn +' = ''' + @GenericValue + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29
SELECT * FROM #UsersTempTable

==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE

View Replies !

Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.

What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).

This is the code in my procedure..

DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list

DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt

select count(*) from @tab_name

This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.

Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.

Please help me to construct the select statement that is similiar to

x=<table name>
select * from x
where x is a variable and the table name gets substituted.

what is the syntax for it ?

View Replies !

How Do I Use A Variable To Specify The Column Name In A Select Statement?
How do I use a variable to specify the column name in a select statement?

declare @columnName <type>

set @columnName='ID'

select @columnName from Table1

View Replies !

Put Select Statement In SSIS Variable
Is it possible to add a variable in SSIS like

name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])

@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made

I know I'm doing something wrong with the data type because it's stores the whole select statement as a string

Help

Worf

View Replies !

How To Create A Select Statement With An Increasement Variable?
Example:

Select icount + 1 as icount from table

or

Select counter() as icount from table

The above is wrong ...just a sample to show what I am trying to accomplish.

Is there a function in SQL statement? Thanks.

View Replies !

Return Variable Name As Part Of Select Statement.
hey all,

I have the following query:

ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]

AS
BEGIN

DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)

DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)

SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count

FROM article

JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id

GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name

HAVING article_id = @article_id

END
GO

as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.

I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.

But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.

And i cant add a join, because it would return the amount of rows of the comment...

unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?

Cheers

View Replies !

Getting A Variable That Has A SELECT Statement To Return Results
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)

@a = Select * from foo ---Obviously mine is much more robust

How do I execute @a to actually Select * from foo?

View Replies !

Storing Results Of Select Statement In @variable

I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:

SET @value = select max(price) from product

but this does not work, can someone tell me how I would go
about in storing the results in a variable.

@value is declared as int

Thanks in advance,
Sharp_At_C

View Replies !

How To Assign The SELECT Statement Output To A Local Variable?

In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?

View Replies !

Unable To Create Variable Select Statement In For Each Loop
What I'm trying to do is this;

I have a table with Year , Account and Amount as fields. I want to

SELECT Year, Account, sum(Amount) AS Amt

FROM GLTable

WHERE Year <= varYear

varYear being a variable which is each year from a query

SELECT Distinct Year FROM GLTable

My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.

What I have done is to defined two variables as follows

Name: varYear (this will hold the year)

Scope: Package

Data type: String

Name:vSQL (This will hold a SQL statement using the varYear)

Scope: Package

Data type: String

Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]

I've created a SQL Task as follows

Result set: Full Result Set

Connection Type: OLE DB

SQL Statement: SELECT DISTINCT Year FROM GLTable

Result Name: 0

Variable Name: User::varYear

Next I created a For Each Loop container with the following parameters

Enumerator: Foreach ADO Enumerator

ADO Object source Variable: User::varYear

Enumeration Mode: Rows in First Table

I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows

Data Access Mode: SQL Command from Variable

Variable Name: User::varYear

However this returns a couple of errors "Statement(s) could not be prepared."

and "Incorrect syntax near '='.".

I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.

Any help would be most appreciated.

Regards,

Bill

View Replies !

How To Write A SELECT Statement And Store The Result In A Session Variable
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns

ContactID

View Replies !

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3

I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary

How do I combine the select statement with the where clause?

Help me...I need help urgently

View Replies !

Use A Table Name As A Variable In The From Statement
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName

View Replies !

Using A Variable To Specify Table Column In Sql Statement??
Hello everyone,
I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases). I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement? For example:
SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE
This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions.
Thanks
Scott

View Replies !

Table Name In Variable - Create Statement
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric

View Replies !

Table Name As Variable In SELECT ?
Is there any suggestions for using such parameters as table name or column name in T-SQL queries ?
Please except EXEC ('string').
My server is 7.0

View Replies !

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
Hi

I've used a temporary table in the stored procedure

I've created it as DECLARE @Temp TABLE (id INT)

in select clause I've used this temp table through the joins..

But I've also used a varchar variable to store my criteria...

which I'm building in the stored procedure

so inorder to use it in the where clause I used

EXEC ('SELECT ....................

FROM @Temp T

LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )

Unfortunately this is not working.
Giving the errror

Must declare the variable '@TempT'.

I had to use Temporary table using #, which I feel is really waste of memory...

Is there a way by which I can use my Table variable in the EXEC statement.

Thanks In advance

View Replies !

Make A Select From A Clr Function To A Table Variable
Hi,
is it somehow possible to make a Select-Statement from a Clr-Funktion to a table variable??

Regards
Pamelia

View Replies !

Affecting SELECT Rows To A Table Variable
Hi,

I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!

ex :
DECLARE @MyTestVar table (
        idTest int NOT NULL,
       anotherColumn int NOT NULL)

SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?

View Replies !

Using Result Of A Select Statement From One Table To Add Data To A Different Table.
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?

View Replies !

Help With Select Statement - From XML Table
I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

View Replies !

Problem Getting Select Data Set For XML Auto, Elements Into A Table Variable
I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.

View Replies !

Invalid Results For Order By On Select Query Against Table Variable
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.

DECLARE @tblCustomRange AS TABLE

(

   RecordID INTEGER IDENTITY(1,1),

   RangeMonth INTEGER,

   RangeDay INTEGER

)

DECLARE @Month INTEGER

DECLARE @Day INTEGER

-- Initialize month and day variables.

SET @Month = 8

SET @Day = 11

-- Insert records into the table variable.
INSERT INTO @tblCustomRange

(RangeMonth, RangeDay) VALUES (1,2)

INSERT INTO @tblCustomRange

(RangeMonth, RangeDay) VALUES (1,27)

INSERT INTO @tblCustomRange

(RangeMonth, RangeDay) VALUES (6,10)

INSERT INTO @tblCustomRange

(RangeMonth, RangeDay) VALUES (9,22)

INSERT INTO @tblCustomRange

(RangeMonth, RangeDay) VALUES (12,16)

-- Select everything from the table variable ordering the results by month, day in
-- descending order

SELECT * FROM @tblCustomRange

WHERE (RangeMonth < @Month) OR

(RangeMonth = @Month AND RangeDay <= @Day)

ORDER BY RangeMonth, RangeDay DESC

I am getting the following resultset:

RecordID RangeMonth RangeDay

-----------     -----------         -----------

2                1                 27

1                1                 2

3                6                 10

I am expecting the following resultset:

RecordID RangeMonth RangeDay

-----------     -----------         -----------

3                6                 10

2                1                 27

1                1                 2

View Replies !

SELECT Statement With An Attendance Table
I have a table that contains the following structure and information:

Code:

UID Member_Number Time_Stamp Status Reason Event Reg F_Name L_Name
18772054062/3/2005 11:48:27 AMInNonenoneNoneWendyBoud
86930082522/3/2005 12:39:35 PMInNonenoneNoneJeremyAhlman
98772054062/3/2005 12:40:20 PMOutNonenoneNoneWendyBoud
106930082522/3/2005 12:40:45 PMOutNonenoneNoneJeremyAhlman
118772054062/3/2005 12:40:50 PMInNonenoneNoneWendyBoud
128772054062/3/2005 12:46:25 PMOutNonenoneNoneWendyBoud

I need to be able to take this information and display it in a data grid so that on each row I see the Member Number, First and Last Name and the In and Out Time.

I am not sure how to group the In and Out times together so that the query knows which time out corresponds to which time time for the member?

Any help is greatly appreciated!

View Replies !

Creating Table With A Select Statement
Dear folks,

create table temptable(eno, ename) as select eno, ename from emp.

here the problem is it is asking for the datatype for the temporary table.

is it not possible to create the temp table without providing the datatypes?

thank you very much.

Vinod

View Replies !

Select Statement To Pull Columns From Same Table For Different Ids
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.

The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.

Thanks

View Replies !

How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

Robert

View Replies !

Create Temporary Table Through Select Statement
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.

View Replies !

Dynamic Create Table Statement Or SELECT INTO
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View Replies !

Dynamic CREATE TABLE Or SELECT INTO Statement
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View Replies !

SQL Query (SELECT Statement) And Table Design
I am wondering if there is a direct query in this case:

I am developing a program to a company which simply sells services
One service may have different prices for different types of clients
The price of any service for any client can change at any time, and I should be able to trace these changes at any time

I made the following tables (simplified): (asterisk for primary key)

(Table)             (Fields)

CLIENT_TYPES :    ID*,        ClientTypeName
SERVICES :    ID*,        ServiceName
PRICES :    ServiceID*, ClientTypeID*, Price,       Date*
ORDERS :    ID*,        Date,           ClientTypeID
ORDER_SERVICES :    OrderID*,   ServiceID*

The field in bold is the area of the question

This is a sample data in the PRICES table:

ServiceID           ClientTypeID    Price    Date1                      1                100        1/1/20072                      1                150        1/1/20071                      2                90         1/1/20072                      2                135        1/1/2007

Now if I want to update a price of service 1 for clienttype 1, I add the following row:

1                      1                100        1/1/2008

So one product for one client can have any number of prices with different dates

The following query:

SELECT * FROM PRICES WHERE ClientTypeID = 1

will retrieve all prices with repeats for a specific client (#1 here)

What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query

If there is commemts on table design please tell me

thanks for any one who provides help

View Replies !

Insert Record Into Temporary Table From A Select Statement
Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chua

View Replies !

SELECT Statement To Return Table Column Names
Is there a SELECT statement that will return a the column names of a given table?

View Replies !

SQL Statement Select From One Table Where Not In Another Table
I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?

View Replies !