Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql
Apr 29, 2007
ok, I am on Day 2 of being brain dead.
I have a database with a table with 2 varchar(25) columns
I have a btton click event that gets the value of the userName, and a text box.
I NEED to insert a new row in a sql database, with the 2 variables.
Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working.
Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.
this is what I got
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
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.
I am trying to insert in table using execute sql task.
I want to pass value of Load_Frequency through parameter
But I am getting below error
[Execute SQL Task] Error: Executing the query "Insert Into [dbo].[ETL_LOAD_MAIN] ( [Load_Fr..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Insert Into [dbo].[ETL_LOAD_MAIN] ( [Load_Frequency] ,[Load_Start_DateTime] ,[Load_Overall_Status] ) Values (?,getdate(),'In Progress')
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime declare @enddate as datetime declare @Line as Integer DECLARE @count INT
set @startdate = '2015-01-01' set @enddate = '2015-01-31'
In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.
This is the current structure:
SET @MktNbr = 10
WHILE @MktNbr < 90
BEGIN
DECLARE@sqlstmt varchar(1000)
SET @Market = '0' + CONVERT(char(2),@MktNbr)
SET @sqlstmt = 'SELECT (columns) INTO dbo.table' + @Market + ' FROM #table WHERE marketcode = ''' + @Market + ''' IF @MktNbr = 50 BEGIN SET @MktNbr = 51 END ELSE IF @MktNbr = 51 BEGIN SET @MktNbr = 52 END ELSE IF @MktNbr = 52 BEGIN SET @MktNbr = 55 END ELSE IF @MktNbr = 55 BEGIN SET @MktNbr = 60 END ELSE BEGIN SET @MktNbr = @MktNbr + 10 END EXEC (@sqlstmt)
END
I'm probably having a blonde moment, but I'm trying to replace the if statements with this:
SET @MktNbr = CASE WHEN @MktNbr = 10 THEN 20 WHEN @MktNbr = 20 THEN 30 WHEN @MktNbr = 30 THEN 40 WHEN @MktNbr = 40 THEN 50 WHEN @MktNbr = 50 THEN 51 WHEN @MktNbr = 51 THEN 52 WHEN @MktNbr = 52 THEN 55 WHEN @MktNbr = 55 THEN 60 WHEN @MktNbr = 60 THEN 70 WHEN @MktNbr = 70 THEN 80 WHEN @MktNbr = 80 THEN 81 ELSE @MktNbr END
Clearly it's wrong because the proc bombs every time with a duplicate table error.
It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?
Hello There: I am running a data flow within a ForEach loop wherein I am computing a value called QuotaGap. When it is 0 I do not want any further execution of the loop. I am using a Conditional Transform within this dataflow that writes a record to a table only when the QuotaGap is NOT 0. However, I am unable to terminate the execution of the loop as I am still within the dataflow.
Now, the computation of the gap requires a value from another variable called NetPurchases. I tried using an ExecuteSQL task in the control flow but could not figure out how to pass the value of the variable NetPurchases into the select statement to compute the gap. For example, the select statement would read:
select (QuotaUpperLimit - ?) As QuotaGap from <<tablename>>
I tried setting the parameter as an input as well as an output and it did not work.
Then I tried passing the entire SQL as a string within a variable. This does not work either because in order to compute the math QuotaUpperLimit - NetPurchases, both variables need to be integers but then you cannot concatenate integres together, which is what we need to do to create the SQL.
The other reason I am going through these hoops I guess is that I have not figured out a way to set the value of a variable within a data flow. I compute the value for QuotaGap within the dataflow in a ForEach loop but I have no way to pass this result to a variable called QuotaGap without using an ExecuteSQL task or another ForEach Loop.
I have spent hours on this simple issue and so have given up and looking to the good friends in this forum for help.
If what I have stated is not clear please let me know and I will try to clarify things a bit.
I have a variable that is created dynamically that will be used as an input for a sql statement. When I display the variable in a text box it looks like this: (PROP_CLASS IN ('" & 100 & "','" & 101 & "' )) The following works when I hard code the values into the SQL statement: strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN ('" & 100 & "','" & 101 & "' )) AND ........."But I get no values returned when I use the variable directly in the sql: strSQL = "SELECT * FROM web_transfer WHERE (PROP_CLASS IN (" & propertyClass & ")) AND ..........." The propertyClass variable is the text string “(PROP_CLASS IN ('" & 100 & "','" & 101 & "' ))“. How do I add this variable to the sql string so that it is recognized. I guess that it is adding some hidden characters. Any help please?? I am a newbie to this, s
I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:
"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +"" As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.
[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hi, I'm trying to put two textbox values together and make an insert parameter out of them. It doesn't give me any errors but the database value is null. Here's the code: This is the function at the top of the page: (the lbl1.text is to control that it gets the value at that point, which it does...)Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim useAU As String = tbUseAmount.Text & " " & ddUnit1.Text
Dim maxSU As String = tbMaxStored.Text & " " & ddUnit2.Text
Dim amountP As New Parameter(useAU)
Dim storedP As New Parameter(maxSU)
lbl1.Text = amountP.ToString
amountP.Name = "useAmountUnit"
storedP.Name = "maxStoredunit"
SqlDataSource1.Insert()
End Sub
The parameters: (with a bunch of other parameters which it takes straight from textboxes and works great) <InsertParameters> <asp:ControlParameter Name="name" Type="String" ControlID="tbName" /> <asp:ControlParameter Name="RSmarkings" Type="String" ControlID="tbRSmarkings" /> <asp:ControlParameter Name="CAS" Type="String" ControlID="tbCAS" /> <asp:ControlParameter Name="precautions" Type="String" ControlID="tbprecautions" /> <asp:Parameter Name="useAmountUnit" Type="String" /> <asp:Parameter Name="maxStoredUnit" Type="String" /> <asp:ControlParameter Name="KTTdate" Type="String" ControlID="tbDate" /> <asp:ControlParameter Name="supplier" Type="String" ControlID="tbsupplier" /> </InsertParameters>
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.
Is there an expression syntax for putting a local variable value into a text box like there is for putting a parameter value? I'm using the report builder via VS
When I create a tableadapter to do an insert it added SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY()) at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field. How would I reference the variable in my .cs file? My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter(); ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id); Thanks for any help
I am new to DTS, but really enjoy it and was wondering if someone could help me with the following small vb app.
I am using the following DTS insert statement to insert records into my table. I have multiple textboxes that needs to be filled and then inserted, none of them exept Nulls. How can I modify my code to insert those textboxes as well as run through the boxes and then check if they have nulls and NOT insert the ones that has nulls?
My form has 9 textboxes. Textbox1, 2, 3 Needs to insert values into the first column. Textbox4, 5, 6 into the second and then Texrbox7, 8, 9 into the last column.
My question is how do I format the following line of code to do what I need?
I am trying to migrate database from old structure to new structure usign SSIS.
The table in new db have extra field that i need to assign it using variable. this is because i have few customer that having different variable value. (meaning for 1 customer, the variable will be fix for all the tables in the database)
my question, without using the Execute Sql Task, can i assign the variable into the the old db destination?
eg my data flow Task is : OLE Db Source - Derived Column - OLE DB Destination.
Example data
Old structure (key = txnID) --------------------- TxnID ChequeNo Bank (chq Bank - Bank in Bank) Amount
I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam
i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?
DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6) SET @LastDate = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales]) Set @LastRunDate = ( select right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )
[code]....
but i get msdtc on server xxxxx is unavaiable?how to get my linked server query into my table
I am able to import a CSV file into a temporary table as long as I know the number of fields in the CSV file. Here is what I would like to do:
I would like to have a CSV file which has UP to 6 entries per row. I would like to insert each row into a table; if the there three fields, then I want to insert them into the first three columns to the temporary table. If there are four, then insert into the first four fields. Is this possible?
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime) declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it! SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName); SELECT @CoDeptRowID = SCOPE_IDENTITY()" <insertparameters> <asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/> <asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/> <asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/> <asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" /> </insertparameters> </asp:SqlDataSource>
Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify bulk insert.... from 'myfilename.txt' BUT my filename will always change and I store it into a variable @filename
When I try to run the bulk insert instruction ... from @filename it doesn't work.. do you know why?
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.
Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
