ADD COLUMN To Variable Table?

Sep 29, 2007

Code Block

ALTER TABLE @OrderPrice ADD(description varchar(50) );


What's wrong?

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related

Using A Variable To Specify Table Column In Sql Statement??

Feb 6, 2008

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 15 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?
 
 

View 4 Replies View Related

SQL Server 2012 :: Table Variable Update Column

May 29, 2014

I am writing a query to update table variable. It is throwing me some error.

I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table

update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID

View 9 Replies View Related

Problem Referencing A Column In A Table Variable In A Query.

Apr 23, 2006

Hello All

I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.

declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable

insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable

UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable

Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable

- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt

UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table


Thanks in advance

smHaig









View 1 Replies View Related

Integration Services :: How To Compare SSIS Variable And Column In Table

Apr 21, 2015

My Requirement IS : 1<sup>st</sup>run: if the record does not exist in the table insert the record (file_name, last_modified_file_date) and create a copy in the archive folder with file_name_currentdate.csv

Daily run: retrieve the last_modified_file_date from the input file and check if the retrieved date is greater than the last_modified_file_date in the table:

If true: create a copy of the input file in the archive folder and update the last_modified_file_date in the table with the retrieved date

If false don’t do nothing because the file has been archived in one of the previous runs.I have already retrieving the modified date and File Nae iserting into Filename Table: (That table has 2 columns which are FileName and FileDate) so In script task everytime the variable getting Modified date(retrieve the last_modified_file_date from the input file). How I can Compre the existing table record and variable. I have already imported the all Filenames and Modified into table like below.

View 3 Replies View Related

Random Selection From Table Variable In Subquery As A Column In Select Statement

Nov 7, 2007

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 14 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Transact SQL :: Calculate Third Column Using Second Column And Variable

Nov 23, 2015

create table #t
(
id int,
col1 decimal(18,2)
)
go

[Code] ...

-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.

-- OUTPUT:

-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00

--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)

View 13 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

How Can I Create A New Table With Its Column Named From Another Table's One Column Value By Using A Select Sentence?

Sep 27, 2006

For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".

can you tell me how can I complete this with the SQL?

View 2 Replies View Related

How To Use A Variable As A Column Name?

May 25, 2008

 Hi, I want to use a variable and set it to column name but it just doesnt' workwhen I execute it. I got an error message "Invalid column name 'column1'. can you please help. ALTER PROCEDURE [dbo].[procedure_name]     @var1            INT = NULLAS    BEGIN        IF(@var1 = '-1')            BEGIN                --SET @var1 = column1            END    SELECT *    FROM table1    WHERE column1 = @var1   Thank you

View 4 Replies View Related

Column Name As Variable

Feb 25, 2001

Using a stored Procedure, I want to select a record from a Table if a certain string appears in a specified column, so I use

CREATE PROCEDURE spSearcher
@String varChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String1,Column1) > 0

which works fine.
But the following doesn't work:

CREATE PROCEDURE spSearcher
@String varChar(20)
@ColumnName VarChar(20)
as
Select Column1, Column2, Column3 from Table1
where Charindex(@String,@ColumnName) > 0


How can I substitute a variable for Column1, so I can pass the name Column1 or Column2, or Column3, as desired?

Thanks in anticipation,

Peter Caspersonn.

View 1 Replies View Related

Column As Variable

Jun 2, 2004

I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.

For example:
I want to execute the following statement but using a variable for the column name:

Select * from tblmet1araw where JulianDay = 1


JulianDay is an integer
This is how I have my code set up:

declare @xxx as varchar(20)
set @theday = 'JulianDay'

select * from tblmet1araw where @theday = 1

I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.

View 2 Replies View Related

Variable Column Name

Aug 11, 2006

Hi,

I have this query where I select data from the last 12 months (in 12 different columns).
What I would like to know is if it's possible to change the name of the columns to the month they refer to.

Thank's in advance,
AR

View 7 Replies View Related

Set Variable To Value In Column

Jul 26, 2007

Is it possible to set a variable in a stored procedure equal to avalue from a column when that column's respective ID is equal tomax(id)-1ID A B1 24 242 53 293 76 474 32 32What I am trying to do is update A in the last column to be equal to Bfrom the 4th row, plus A from the 3rd row. If I could set a variableequal to A in row 3 (in this case 76) I could easily add the variableto the existing value.I've tried something like this before. I'm sure some of you will sayright away, "of coarse that doesn't work". I'm new to sql.Set A = A + select( B from mytable where ID = (select (max(ID)-1) frommytable))where ID = (select (max(ID)) from mytable)ThanksMatt

View 1 Replies View Related

Get First Row First Column Value Into A Variable

Sep 28, 2006

It is my second time on this forum

Nice people managed to help me on my first thread. I am encouraged to proceed with you !

Well   I  have an Excel source that returns One row , two columns: what I want is get the value returned in  the first row - first colum into a variable so I can then execute sql tasks based on this filter parameter !

Can you guide me ? 

Regards Jacques

 

View 5 Replies View Related

Column Name As Variable

Oct 1, 2006

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

View 4 Replies View Related

USING A VARIABLE AS A COLUMN NAME

May 28, 2008

I have a Windows programme using VS STudio 2005 / C# /Windows XP and

Microsoft.Practices.EnterpriseLibrary.Data

I am trying to pass an 'in parameter' to a stored procedure which will be used in the sp as a column name . The column names in the database are numbers (in this case, 0 to 45). I wish to pass the column name from my Windows programme tot he sp a and use it in the sp as the variable @BallColumnValue. Can anyone please advise me of the correct way to do this?

Thanking you in anticipation.

lpbcorp

Windows programme along the lines of:

else if (subsetList == false)

{

db.AddInParameter(dbUpdateBallSuccess, "BallColumnValue", DbType.Int16, i);

db.AddInParameter(dbUpdateBallSuccess, "BallNo", DbType.Int32, 1);

}

db.ExecuteNonQuery(dbUpdateBallSuccess);

Stored procedure is as follows:


USE [MyDatabaseName]

GO

/****** Object: StoredProcedure [dbo].[MyTableNameInsertMainDrawBallRecency;] Script Date: 05/22/2008 22:12:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[MyTableNameInsertMainDrawBallRecency;]

(

@BallColumnValue nvarchar(10),

@Recency int,

@date DateTime,

@weeksSinceDrawn int,

@priorWeeksSinceDrawn int

)

AS

DECLARE lastrow_cursor CURSOR SCROLL FOR

SELECT CAST(@BallColumnValue AS COLUMN) FROM MyTableNameRecencyMainDrawBall

OPEN lastrow_cursor

FETCH LAST FROM lastrow_cursor INTO @weeksSinceDrawn

FETCH PRIOR FROM lastrow_cursor INTO @priorWeeksSinceDrawn

SELECT @priorWeeksSinceDrawn

IF @Recency = 0

BEGIN UPDATE AuMondayLottoRecencyMainDrawBall

SET [@BallColumnValue] = @Recency

WHERE Date = @date

END

ELSE

UPDATE MyTableNameRecencyMainDrawBall

SET [@BallColumnValue] = @Recency + @priorWeeksSinceDrawn

WHERE Date = @date



CLOSE lastrow_cursor

DEALLOCATE lastrow_cursor

View 10 Replies View Related

A Table/column To Table/column Data Check (was Help Please, SQL Something Simple)

Sep 15, 2006

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

View 6 Replies View Related

Using A Variable For A Column Name In A Query

Sep 22, 2006

Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE      CASE WHEN @Field = 'Fname' THEN Fname = @Value END,     CASE WHEN @Field  = 'Lname' THEN Lname = @Value END,     CASE WHEN @Field  = 'Phone' THEN Phone = @Value END;

View 1 Replies View Related

Column Name As Variable In Trigger

Jun 7, 2001

I am writing an audit trail trigger that needs to query columns, but the specific column names are not known until the trigger runs and determines what columns have changed. Once I know the changed column(s) I fetch the column name. No sweat.
The problem is using a variable in place of a column name I can not get the data to return, only the column name.

-- Here is how I get the column name. It works great, @CName
-- reflects the column name as I use it elsewhere fine.
Select @CName = col_name(OBJECT_ID('TABContract'), @CNum)

-- This simple syntax usually works, it does not using a
-- variable as the column name. @Prev is populated with
-- @CName literally. I have tried various and asundry ()'s and []'s
Select @Prev = @CName From Deleted

-- Here I have hard coded the Column name and ran a test modifying that column "Comment" and it works! @Prev is populated with the columns data not the Column name.
Select @Prev = Comment From Deleted

-- Tried these syntaxes, no luck
Select @Prev = (Select @CName From Deleted)
Select @Prev = (Select col_name(OBJECT_ID('TABContract'), @CNum) From Deleted)


Any help appreciated,
Ray
rwood@semcorp.com

View 2 Replies View Related

Variable ORDER BY Column

Jan 20, 2000

I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.

View 1 Replies View Related

Variable Column Queries

Jul 30, 1999

I have assigned the name of columns to a variable (@colum_name):


set @col_name = (select name from syscolumns
where id = object_id('table_name') and colid = @counter


The value of @col_name changes as the counter iterates through the different columns. The PROBLEM IS RUNNING THE FOLLOWING QUERY:


select @col_name from table_name where 'primary key = xyz'

The query returns the value of the variable @col_name. I want the value of the column in the table with name = @col_name.



I am new to T-SQL so please feel free to point out any mistakes.

Any suggestions would be greatly appreciated.

Thanks in advance.
Shaleen

View 1 Replies View Related

Temp Variable As Column Name ??

Jul 21, 2004

Hi ,
Can anyone guide me to resolve my problem . I need to write a procedure which first looks for the Worker names from WORKER table who satisfies certain criterias , and then Find from another table how many jobs each one has done on each day of a month . I have written a function which will return all days of a particular month, which can be used for the above procedure .If the wrokers are John , Alex and Martin ,( which may vary according to the branch parameter) The report should look like

Day John Alex Martin
1/5/2004 4 8 NULL
2/5/2004 5 9 12
------------------------
etc

Thanks in advance
Regards
Praveen ( praveenvc@rediffmail.com)

View 2 Replies View Related

How To Eval() A Variable To Get A Column Name?

Jan 20, 2004

OK.. I've got a stored procedure I'm writing, which accepts an argument called @statfield... let's say I want to use this variable as a literal part of a SQL statement, example:

select * from table1 where @statfield = @value

I want to do basically an eval(@statfield) so if @statfield is "key_id", then the select statement comes out:

select * from table1 where key_id = @value

How can I do this?

Thanks!

View 5 Replies View Related

Variable Column Names

Mar 15, 2004

I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...

Is there any way to accomplish this???

I thought about placing these values into a temp table...

This is what I have so far...

Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)

Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin

Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End

View 1 Replies View Related

How To Get Multiple Column Value To Variable In PDW

Mar 5, 2014

how to get multiple column value to variable in PDW/DSQL?such as below

declare @a table(col1 int, col2 int)
insert into @a values (1,2)
declare @c int, @d int
select @c = col1, @d = col2
from @a
select @c, @d

View 3 Replies View Related

How To Insert Variable In One Column

Jan 9, 2008

Hi there

I am migrating all my DTSs to SSISs. One of them is a very basic DTS, that copies data from a text file and places it in a table with one extra column. That extra column is populated with the value of a global variable.

In DTS mode I was creating a custom ActiveX transformation stating
DTSDestination("FieldName")=DTSGlobalVariables("VariableName").Value

Can any one advise me on the best way to accomplish this in the SSIS?
I tried several approaches but all failing.

Many thanks
ds9

View 3 Replies View Related

How To Add The Value Of A Variable To Column In A DataSet

Sep 8, 2006

Hi,

i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".

What i need is a way to add a column to my transformed data which contains a value from a User-Variable.

My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.

So anybody know of an existing Data Flow component, which can do this?

Regards, Martin

View 3 Replies View Related

Mapping Of An Xml Column To Variable

Aug 23, 2006

I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:

Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.

I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?

View 3 Replies View Related

Assigning Value Of A Variable To A Column Name

Mar 25, 2008



Hi,

I would like to assign the value of a variable to a column name of a table. For eg:

declare @sam varchar(100)
set @sam = 'Insert'
create table #temp(Sample varchar(100) not null)
insert into #temp(Sample) Values(@sam)
drop table #temp

I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.

Thanks,
Sana.

View 1 Replies View Related







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