What would be the correct syntax, if any, that allows updating a table
variable in a statement that uses the same table variable in a correlated
subquery? Here's an example:
DECLARE @t table (N1 int NOT NULL, N2 int NOT NULL)
UPDATE @t SET
N1 = (SELECT COUNT(1)
FROM @t AS t
WHERE 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 make
it work, but rather in a general way to refer to table variables in the
contexts where correlation names cannot be used.
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)
Hi, I am trying to build a website in C# for a university project, and am having trouble updating a table from the code (at which I'm not too hot on writing). I can manage to get the SQL syntax to work in SQL Server 2005, but when I transfer it to the Visual Web Developer page it doesn't update. Would anyone be able to kindly point out where I am going wrong? My code (apologies for the amount) is;protected void CalculateEmissionsAndTotal() { //Create the ConnectionSqlConnection sqlConn = new SqlConnection("server=Acer-Laptop\SQLEXPRESS; database=NeuCar; Trusted_Connection=true"); //Open the connection sqlConn.Open(); //Create the Command, passing in the SQL statement and the ConnectionString queryString = "DECLARE @Today DATETIME SELECT @Today = Current_Timestamp Declare @TotalCharge int DECLARE @EmissionsUrban int DECLARE @EmissionsCountry int SELECT @TotalCharge = ((MemberPayment.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) + ((MemberPayment.MileageCountry * Vehicle.EmissionsPerGramUrban) * 0.05) FROM [NeuCar].[dbo].[MemberPayment] JOIN [NeuCar].[dbo].[Vehicle] ON MemberPayment.Registration = Vehicle.Registration JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName WHERE (Member.UserName = @myUserName) AND (MemberPayment.PaymentDate >= DATEADD(dd,-(DAY(DATEADD(mm,-1,@Today))-1),DATEADD(mm,0,@Today))) SELECT @EmissionsUrban = ((MemberPayment.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) FROM [NeuCar].[dbo].[MemberPayment] JOIN [NeuCar].[dbo].[Vehicle] ON MemberPayment.Registration = Vehicle.Registration JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName WHERE (Member.UserName = @myUserName) AND (MemberPayment.PaymentDate >= DATEADD(dd,-(DAY(DATEADD(mm,-1,@Today))-1),DATEADD(mm,0,@Today))) SELECT @EmissionsCountry = ((MemberPayment.MileageCountry * Vehicle.EmissionsPerGramCountry) * 0.05) FROM [NeuCar].[dbo].[MemberPayment] JOIN [NeuCar].[dbo].[Vehicle] ON MemberPayment.Registration = Vehicle.Registration JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName WHERE (Member.UserName = @myUserName) AND (MemberPayment.PaymentDate >= DATEADD(dd,-(DAY(DATEADD(mm,-1,@Today))-1),DATEADD(mm,0,@Today))) UPDATE MemberPayment SET TotalCharge = @TotalCharge, EmissionsUrban = @EmissionsUrban, EmissionsCountry = @EmissionsCountry WHERE (Registration = @myRegistration); "; SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUserName", Session["sUserName"])); cmd.Parameters.Add(new SqlParameter("@myRegistration", Session["sRegistration"]));MileageLabel.Text = "*Update complete"; //Close the connection sqlConn.Close(); } I would be very grateful if anybody could help, Kind regards, Chima
I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.
I have performed the process manually running the stored procedures, providing the values directly and everything works fine.
I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.
One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.
This has become rather frustrating and would appreciate any assistance.
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number). I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance. The database table is called "friday" and the one and only column is called "Tickets". Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn; conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"); conn.Open();int count = -1; SqlCommand cmd = new SqlCommand("select Tickets from friday", conn); count = (int)cmd.ExecuteScalar();if (count > 0) { string updateString = @" update friday set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); } else { } conn.Close(); }
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
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?
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)
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
Hi All, I have a Problem while updating one table data from another table's data using sql server 2000. I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms) I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master UPDATE [dbname].dbo.table SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1 WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2 AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3 AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error: Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
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
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
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");
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?
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
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
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
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)?
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
I want to write trigger code to update values in table1 to table2. ALTER, CREATE, DROP cannot be used in a trigger, I guess.
Tabel1 ID, Name, Asset, Date, Active --------------------------------- 1, A, 10 , 01/08/04 Y 1, B, 16 , 06/08/04 Y 1, C, 12 , 07/08/04 Y 1, D, 13 , 10/08/04 Y 2, E, 10 , 15/08/04 Y 2, F, 11 , 16/08/04 Y 2, Y, 12 , 01/08/04 N 2, G, 15 , 17/08/04 Y 3, H, 13 , 19/08/04 Y 3, I, 15 , 02/08/04 N
As you can see, code must select only active('Y') names in table1 arrange in descending order of asset for each ID and updates(or insert into?)table2. Please help me with code
How do I update a table from another table which resides in a different database but on the same server. Example Update table a which is in database 1 from table b which is database 2. Please help I need this as soon as possible. Thanks in advance to anyone who can help.
Accidents, Vehicles, Casualties. the relationship is many vehicles to an accident and many casualties to a an accident.
I have one new table which will contain all the vehicles related to all accidents. however i want to be able put in all casualties that every vehicle is related to..so within the new table, there are three severities of casualties Vehicledetails column severity1 severity2 severity3
For every vehicle where there is 0 or more casualties I want to be able put a (total )number for each category of casualty( severity in the VehicleDeatils table ) into the columns in vehicledriverdetails
how can i do this?? i dont want ot have to manually update every single entry one by one...
