Qualifying Table Variables

Jan 6, 2004

hi all!





I am using table variables instead of creating a temp table because it seems to be faster





But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?





ex: with temp table





create table #tmp.... (F1...)


#tmp.f1





with table variable





declare @temp table(...


@table.f1 - can´t do it





the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name








thank to all and happiness for all 2004

View 2 Replies


ADVERTISEMENT

Qualifying What Goes In An Aggregate

Jun 15, 2006

Greetings!

I'm hoping for a little insight for a novice.

Here's what I have:

1 table that looks like this...


Code:


rep_id answer
1 2
1 1
2 1
3 0
3 1
3 1
4 2




What I want is a recordset returned as ... (columns that tally the 1s and 2s respectively with the rep's id#)


Code:


rep_id total1 total2
1 1 1
2 1 0
3 2 0
4 0 1




What I can do so far is get individual recordsets returned for either total1 or total2 but not together.

Here's what I'm using for these...


Code:


SELECT rep_id, COUNT(answer) as total1
FROM myTable
WHERE answer = '1'
GROUP BY rep_id



and


Code:


SELECT rep_id, COUNT(answer) as total2
FROM myTable
WHERE answer = '2'
GROUP BY rep_id



So how do I form a SQL statement that will combine the 2 columns I have created into 1 joined by the rep_id?


To make it a little stickier there may be some nulls in the existing answers but hopefully that is not going to bung me up.

Any help will be thankfully received!!!

View 2 Replies View Related

Select Only The First Qualifying Record In SQL Express

Jan 13, 2006

Not sure if my description was accurate enough. Here is what I am trying to do. I am learning ASP.Net and am playing around with adding photo albums to my site.  I would like a page listing all of the photo albums in a details view.  For each album, I want to list the album name and then show a thumbnail of the FIRST photo in that album. When the user clicks on the thumbnail, he will be taken to the page showing all pictures in the album.
My problem is coming up with the SELECT statement to return each album and ONLY the first photo in each album. I have 2 tables: albums and photos. Albums has albumid, ownerid and albumname. Photos has photoid, albumid, photoname, and photothumburl.
I know I want to SELECT albums.albumid, albums.ownerid, albums.albumname, photo.photoid, photo.photothumburl FROM (I am going to mess up this syntax because I am not in front of my dev computer) albums INNER JOIN photos ON albums.albumid = photos.albumid....
But I do not know how to tell the SELECT to return only the photoid's that are the FIRST photo in the album. I can get it to work if I only use the photos table and just SELECT albumid, MIN(photoid) FROM Photos, but once I add in all the other items I want the query to return (album name, ownerid, photothumburl), I have to put them all as "Group By" and the resulting group of records I get includes all photos regardless of whether they are the first photo in the album or not. Hopefully that makes sense to people. I really would appreciate any help you can give me. So far this stuff is really fun...

View 1 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Update One Table When Records Inserted In Another Table - Variables In Trigger

May 19, 2014

I am trying to update one table when records are inserted in another table.

I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.

My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:

ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)

[Code] ....

View 4 Replies View Related

Table Variables

Feb 2, 2006

Hello,I am writing a function that uses two table variables. The structures ofboth are shown here:DECLARE @workdates TABLE (conflict CHAR(1),workdate SMALLDATETIME)DECLARE @existing TABLE (workdate SMALLDATETIME)I need to do an update on the first table:UPDATE @workdatesSET conflict = 'X'FROM @existing sWHERE workdate = s.workdateI am concerned that the unqualified 'workdate' in the WHERE clause willgive me an ambiguous column reference. Is this SQL statement valid?Thanks,Andrew

View 3 Replies View Related

TABLE Variables

Jul 20, 2005

Hey everyone,I read in a SQL Server book that you can now create a tablevariable.DECLARE @TMP TABLE (list of fields)Then you can you can use the statementINSERT INTO @TMPSELECT (whatever).I've tried it and it works. The book also says that youshould be able to pass these variables between storedprocedures and functions. Problem is, when I try todeclare the variable at the top of the procedure, thesyntax checker hates it.Anyone else out there try this out?SAM

View 1 Replies View Related

Table Variables

May 9, 2007

Will reporting services allow the use of table variables in the SQL query?

View 1 Replies View Related

Table Variables

Jun 11, 2007

I want to querry a table based on the input of a form, I have tables Monday, Tuesday ect., and want to display the records for Monday if the user selects Monday in a menu. I need to delcare a table name but am not sure how?

DELCARE @myVar char(20)
SET myVar = Request.Form("select2");



"SELECT * FROM @myVar"



View 2 Replies View Related

Table Variables

Apr 28, 2008



I know user defined global table variables are not allowed in sql. I'm trying to avoid using temporty tables for speed reasons. I have a function in which a table variable is defined, and a function within that function that needs to call that table variable. Any ideas?

Thanks

View 1 Replies View Related

Table Variables/Table Reference - Is This Possible?

Feb 28, 2005

Is it possible to do something like this in SQL:

DECLARE @TABLE table

if @GOOD = 1 BEGIN
Set @Table = Table1
END ELSE BEGIN
Set @Table = OtherTable
END

SELECT * FROM @TABLE

View 1 Replies View Related

Table Variables And Calculations

May 4, 2007

 So, I've got a problem with using table variable "fields" and a simple variable in calculations. It ain't workin'. See the bolded code below. When I run the SP, it returns 0 for those values. Anyone got any clues? Is this a table variable limitation?  ALTER PROCEDURE YearlyTotalsInPercentages(@Year int) ASBEGINDECLARE @TotalSum intDECLARE @Totals TABLE
(
CBDCYearlyTotals int, ProductLine varchar(50))INSERT INTO @Totals (CBDCYearlyTotals, ProductLine)SELECT SUM(dbo.Main.Hours), dbo.Project.ProductLineFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, @TotalSum AS TotalSum, ROUND((t.CBDCYearlyTotals/@TotalSum) * 100, 1) AS Percentage FROM @Totals tEND
GO
  Thanks Yall

View 1 Replies View Related

Dynamic SQL And Table Variables...?

Aug 23, 2004

Hello, all. I'm attempting to insert data into a table variable using dynamic SQL created at runtime.

However, with a Table variable, SQL server will not allow the EXEC method to be used in an INSERT statement.

How do I go about this?

View 2 Replies View Related

Using Variables In A Temp Table Name

May 4, 2000

I am trying to add a variable to a temporary table name. Throughout a stored procedure, I do a lot with this table. I delete, insert, update, and query this table. Is there a way to do the following without having to set the entire 'select * from ...' line as a variable? Below is what I am trying to accomplish. It all works until the select * line. Is there a way to accomplish what I am trying to do below?

Declare @table varchar(255),
@PassedID integer
set @passedID=5

set @table="test"+CONVERT(varchar(20), @passedID)

select * from @table

Thanks,
Jon

View 3 Replies View Related

Table Names And Variables

Nov 14, 2005

I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?

BTW both variables are of type char(100)

View 1 Replies View Related

Count Two Variables In Table

Aug 23, 2013

I have a table that has two columns POLICY_NUMBER and POLICY_TYPE

POLICY_NUMBER POLICY-TYPE
123 1
123 1
123 1
567 2
567 2
789 1
789 1
345 1
345 1
345 1

I need to write a script that give me the following result

policy_type_count policy_type

3 1
1 2

View 3 Replies View Related

Trigger And Table Variables

Nov 6, 2014

Can we use tables variables in triggers... if yes, any example...

View 4 Replies View Related

Table Variables And Identities

Feb 16, 2006

Can an identity be created in a table variable?

Can joins be performed between table variables to be inserted into another table variable?

Am I better of using a temporary table?

(I'm working in a stored procedure here)

Thanks in advance

View 10 Replies View Related

Variables In The FROM (table Name) Clause

Jul 20, 2005

I have a problem with executing following T-SQL select query.My select statement looks like thisSELECT * FROM (@TableName_FirstPart + @TableName_secondPart)**@TableName_FirstPart & @TableName_SecondPart are Local variables**Im getting Incorrect syntax error for this statement.Following is the error message:"Server: Msg 170, Level 15, State 1, Line 55Line 55: Incorrect syntax near '+'."Is it possible to construct table names in the FROM clause dynamically ?Thanks in advance

View 2 Replies View Related

How To SET Multiple Variables From One Table Record?

Apr 18, 2005

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
 

View 2 Replies View Related

Table Variables Vs. Temp Tables

Mar 8, 2006

How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead.
Short of testing the stored proc both ways, how do I know what to do?
declare @Temp table
or
create table #Temp

View 1 Replies View Related

Using Variables For Table Names In Query

Mar 19, 2006

Is it possible to use variable name to dynamically define a query in a stored procedure?  EX:

@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category

SELECT @Column
FROM @Table

View 1 Replies View Related

Table Variables - ANSI Compliant?

Jun 21, 2001

This should be a relatively easy one... Are table variables, the data type, SQL (92 or 99) compliant?

TIA,

R2ro

View 1 Replies View Related

Moving Data From One Table To Another Using Variables

Oct 20, 2013

I need to move data from one table to another using variable percentages and business days.

Here is the basic idea:

Variables: varTable1, varTable2, varPercentage

Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.

View 1 Replies View Related

Use Of Variables In Table Names And Fields

Jun 22, 2006

Hi, I am new to using SQL for anything more in depth than querying and reporting.

I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?

quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)

set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'

create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);

insert into @a_tmptbl values ( "foobar", 21.76, 1);

select * from @a_tmptbl;

The error I am getting is:

quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.


Any advice would be gratefully accepted

View 3 Replies View Related

Syntax For Updating Table Variables

Jul 23, 2005

What would be the correct syntax, if any, that allows updating a tablevariable in a statement that uses the same table variable in a correlatedsubquery? Here's an example:DECLARE @t table (N1 int NOT NULL, N2 int NOT NULL)UPDATE @t SETN1 = (SELECT COUNT(1)FROM @t AS tWHERE t.N2 < @t.N2)This doesn't compile, complaining about "variable @t" in the WHERE clause.I'm not so interested in a way to rewrite this particular statement to makeit work, but rather in a general way to refer to table variables in thecontexts where correlation names cannot be used.Thank you.--remove a 9 to reply by email

View 7 Replies View Related

Temp Tables Vs Table Variables

Dec 15, 2005

I am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you

View 1 Replies View Related

Syntax Of Updating Table Variables?

Mar 10, 2006

Hi,I have a user-defined function which returns a table (call it '@a'),and has another table defined as a variable (call it '@b'). When I tryto do the following query, I get "Must declare the variable '@b'" and"Must declare the variable '@a'." How do I remedy this?The query:UPDATE @aSETstuff =(SELECT otherStuff From @bWHERE @b.someID = @a.someID)

View 2 Replies View Related

Table Variables And Update Statements

Jul 20, 2005

HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew

View 2 Replies View Related

Look Up The Table In The Different Server And The Name Should Be Passed By Variables

Aug 16, 2007

Hello,

I need to check if the IDs in table A on the server X exist in table B (if not check table C, if not in C, check table D ) on the server Y. The problem is that the table name changes every year and I need to use the variable for the reference table name in LookUp component in the Dataflow Task.

Can anyone help me?

View 1 Replies View Related

Is It Possible To Index Local Table Variables?

May 7, 2008

I have a function that returns a table of matching names. For the most part it returns under 5,000 results which doesn't cause too much lag joining to the search report data (which is indexed). But sometimes there might be 10,000 + search name results. I find this and higher can cause excessive lag joining. Is it possible to index this result table from the function? Or any other suggestions?

THANKS

View 8 Replies View Related

Sql Server 2005 - CTE Vs Table Variables

Nov 16, 2006

Hi,
What is the difference between CTE and table variables.
I read that CTE is used so that the tables do not get persisted in the database. Do the table variables have the same efficiency? i.e. not persisted in the database.
Can you throw some light over here if you understand the two concepts clearly please?

Thanks

View 5 Replies View Related

Table Variables In Stored Procedure

Jan 11, 2006

I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS.  I know there was a problem doing this in DTS, which would result in an Invalid Pointer error.  I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider."  The stored procedure runs fine on it's own.

Any thoughts?

 

 

View 24 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved