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">
<script runat="server">
protected void runit_Click(object sender, EventArgs e)
{
//SqlDataSource ID = "InsertExtraInfo".Insert();
//SqlDataSource1.Insert();
}
protected void Button1_Click1(object sender, EventArgs e)
{
SqlDataSource newsql;
newsql.InsertParameters.Add("@name", "Dan");
newsql.InsertParameters.Add("@color", "rose");
String t_c = "purple";
string tempname = Page.User.Identity.Name;
Label1.Text = tempname;
Label2.Text = t_c;
newsql.Insert();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>mini update</title>
</head>
<body>
<form id="form1" runat="server">
name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />
color
<asp:TextBox ID="color" runat="server"></asp:TextBox><br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />
<br />
set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />
Lable 2 =>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />
Usernmae=><asp:LoginName ID="LoginName1" runat="server" />
<br />
<br />
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"
InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"
UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">
<DeleteParameters>
<asp:Parameter Name="original_name" Type="String" />
<asp:Parameter Name="original_color" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="color" Type="String" />
<asp:Parameter Name="original_name" Type="String" />
<asp:Parameter Name="original_color" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:InsertParameter("@name", "Dan", Type="String" />
<asp:InsertParameter("@color", "rose") Type="String"/>
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />
<asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />
<asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
View 1 Replies
ADVERTISEMENT
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
Aug 7, 2014
I need to create a stored procedures, this procedure will receive two parameters:
@TableName
@KeyField
I need to storage into a variable the values of ALL THE FIELDS separeted by |
For example if my table is Customers
CustomerID CustomerName City
111 Adventure Boston
222 Pubs NY
And I execute EXEC mysp 'Customers',111
I need a return value like this
@ReturnValue = 111|Adventure|Boston
I create something like this
CREATE PROCEDURE my sp
@table varchar
@key numeric
AS
@field varchar(40),
@object int
Select @object = object_id from systables where name =@table
--The instructions to create a cursor
WHILE (@@FETCH_STATUS = 0 )
Select @object = name from sys.columns where object_id = @object
--The instructions to close and deallocate the cursor
I have alredy to save the value using the EXEC command but is not working; something like
@String = Select @object from @table where CustomerID = @keyValue
View 1 Replies
View Related
May 12, 2015
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')
View 2 Replies
View Related
Feb 26, 2015
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'
[Code] .....
View 1 Replies
View Related
Sep 11, 2007
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?
View 14 Replies
View Related
Feb 15, 2008
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.
Thanks!
View 7 Replies
View Related
May 27, 2008
Hi,
I am trying to create an SSIS package but am not able to set the global variable values.
I want to have a Global Variable as @EventID
and the create a Execute SQL Task which will run this query:
SELECT Max(EventID)
FROM EventTable
and assign this Max value to the global variable @EventID
How can I achieve this...help me please
Regards,
Nusrath
View 1 Replies
View Related
Jan 10, 2008
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
View 5 Replies
View Related
Sep 28, 2006
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.
Where am I wrong?
TIA
View 12 Replies
View Related
Sep 14, 2006
hi,
I have two question
string a;
int i;
insert into sss (id, name) values(.....)
as id is integer and name is nvarchar in the database.
how can I write this variable within the values paranthesis
2) how to adjust a column in sql server 2005 to auto number
thanks
View 3 Replies
View Related
Nov 7, 2007
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>
Any clues?
View 2 Replies
View Related
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
Jul 23, 2005
Is it possible to take the result from an SP that returns 1 value andinsert it into a variable?
View 2 Replies
View Related
Mar 28, 2008
I have table with Rows
Row1 Row2 ............. Row(n)
I want write query which will change the number on the end of Row(number) AS
DECLARE @I int
SET @I = 0
INSERT INTO table(Row(I)) VALUES ('blablabla')
A then
SET i = i + 1 (Increment variable I)
View 5 Replies
View Related
Oct 31, 2007
This is on SQL 2000 and 2005. I need to increment a variable on insert, but am having a hard time getting a workable solution:
declare @var int
set @var = (select max(id) from table1)
insert into table1
select val1,
@var +1 as val2
from table2
where blah and blah and blah
If I were trying to insert val1's of (5, 10, 20) with @var having an initial value of 100, the added rows should be:
val1 val2
5 101
10 102
20 103
Does anyone know how to do this without temp tables (or table variables)?
View 5 Replies
View Related
Dec 15, 2006
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
View 4 Replies
View Related
May 4, 2008
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
View 2 Replies
View Related
Oct 18, 2013
I have created a .bat file for my sqlcmd
my sqlcmd includes a .sql file that builds up my email with a HTML body.
Is it possible to insert a variable (@customername) into the HTML body?
I am already using @maillist and select statement to create my customer email address's.
View 5 Replies
View Related
Jan 9, 2007
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?
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
I think it is something like this, but I am Really not sure and some help would be greatly appretiated:
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('Textbox1', 'Textbox4', 'Textbox7')"
I am really not sure.
Here is all the code:
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
'Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ExecuteSQLTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask1.Description = "Execute SQL Task: undefined"
oCustomTask1.SQLStatement = "Insert into TestTable (Test1, Test2, Test3) " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "Values ('1rowst', '2rowst', '3rowst')"
oCustomTask1.ConnectionID = 1
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add(oTask)
oCustomTask1 = Nothing
oTask = Nothing
End Sub
View 10 Replies
View Related
Nov 29, 2007
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
New Structure (key = TxnID & CoCode)
-------------------------
TxnID
ChequeNo
ChqBank
BankInBank
Amount
CoCode
TQ.
View 6 Replies
View Related
Sep 20, 2006
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
View 3 Replies
View Related
Aug 7, 2006
hi, good day,
if i would like to pass a parameter into store procedure for bulk insert command as below
BULK INSERT [mytable] FROM
@FILE_PATH
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '')
howto i put @FILEPATH in above query inside store procedure ?
thanks for guidance :)
View 2 Replies
View Related
Nov 6, 2014
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
View 3 Replies
View Related
Feb 21, 2007
Hello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?
here is the image
View 6 Replies
View Related
Dec 6, 2007
Hi folks....
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?
Does anyone have any suggestions?
Thanks!
Forch
View 4 Replies
View Related
Aug 28, 2007
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'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
May 24, 2007
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>
View 5 Replies
View Related
Mar 25, 2004
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?
Thank you in advance
View 1 Replies
View Related
May 14, 2008
Hello,
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.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
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.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Jul 9, 2015
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.
select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
ALTER PROCEDURE [dbo].[PKG_OBJ_API$CREATE_OBJ]
(
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@Arrlist varchar(max),
[code]....
View 3 Replies
View Related
Apr 30, 2015
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.
View 2 Replies
View Related
