Insert Increment Variable

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


ADVERTISEMENT

Increment A Variable

Jul 19, 2007



Hi,



How do I increment the value of a variable? let's say from initial value of var1 = 0 it becomes 1



Thanks!



cherriesh

View 8 Replies View Related

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">        &nbsp;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" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<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>        &nbsp;        <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 View Related

Highlighted Row, Increment Variable

May 5, 2008

I have an asp.net with C# code behind page that displays the following. The information
is being pulled from an SQL database. Each row that is above 7:10am is highlighted.:
EntryDate    EntryTime     Employee4/25/08      7:05:56 AM   
Scot                   4/25/08      7:11:02 AM    Rick        4/26/08     
9:05:00 AM    Jen                        4/27/08      8:05:16 AM   
Mel                        4/27/08      7:15:06 AM   
Bill                        4/28/08      5:00:55 AM    Scot I want to have a static variable for each employee called lateScot, lateJen, lateMel, lateRick, lateBill. For each row that is highlighted, I want to increment the counter of the employee by one. I am using the RowDataBound event to highlight as needed. But how can I use the SqlDataSource1_Selecting event to go through the and increment as needed based on highlighted rows? protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {            }protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)    {        if (e.Row.RowType == DataControlRowType.DataRow)        {            string grr = e.Row.Cells[2].Text;            DateTime ontime = Convert.ToDateTime(e.Row.Cells[1].Text);            DateTime late = Convert.ToDateTime("12/30/1899 7:10:00 AM");                        if (ontime.TimeOfDay >= late.TimeOfDay && grr != "Mailman,Mailman" && grr != "Moses,Bill" && grr != "Bathroom,Women's" && grr != "Bathroom,Men's" && grr != "Sharm,Tonmoy")            {                e.Row.BackColor = System.Drawing.Color.Firebrick;                e.Row.ForeColor = System.Drawing.Color.White;            }                               }    } Thanks in advance,Scot 

View 3 Replies View Related

How To Increment SSIS Variable (Date)

Apr 11, 2008

My requirement demands me to use a extract based on a date. And on success i need to increment this date by a day.
So i have a declared two variables, vDate and vSourceSQL. I am using the sql statement from variable in the data access method. My package is successful.
Reading through the forum and after following this blog
http://blogs.conchango.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx
i now have a script task which will increment my "vDate" variable and i am calling it as a post execute task. I have posted the error below. Tried to get atleast the sample provided to succeed, but.... this is what i get.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (800)" wrote 12 rows.
Error: 0xC001405C at Script Task Data Flow Task: A deadlock was detected while trying to lock variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Error: 0xC001405D at Script Task Data Flow Task: A deadlock was detected while trying to lock variables "System::InteractiveMode" for read access and variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Error: 0x2 at Script Task Data Flow Task: The script threw an exception: A deadlock was detected while trying to lock variables "User::vMyVar" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Task failed: Script Task Data Flow Task
Warning: 0x80019002 at OnPostExecute: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at SqlCommandFromVariable: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "SqlCommandFromVariable.dtsx" finished: Failure.
The program '[2316] SqlCommandFromVariable.dtsx: DTS' has exited with code 0 (0x0).
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

View 7 Replies View Related

Auto Increment For INSERT INTO

Dec 19, 2006

I'm populating a table (TMP_SQL) with data from several other tables in our database.

I need to create through SQL commands a counter variable to fill TMP_SQL's KEY1 field. The data I'm pulling in from other tables does not have a field that is always 100% gaurenteed unique, so I need to create a counter for a key field.

Pseudo code:

insert into TMP_SQL(tmp_key1, tmp_general1, tmp_general2, tmp_number1, ....)
select @counter, someColumn, someColumn2, (select column3 from other table returning 1 row),...
from table1 where things = things.


How can I implement @counter = @counter + 1? Is this possible? I tried:
DECLARE @COUNTER INT
SET @COUNTER = 0

and then had @COUNTER = @COUNTER + 1 in my select statement; of course, I got the error about not being able to set variables in the same select as data-retrieval

Computers never do what I tell them to...

View 1 Replies View Related

How To Insert Or Update Increment Of 2 ?

Jan 21, 2008

HI

I have table with 20 columns. One of the column(col1) in the table has value like below

Col1 -----------Col2
123------------- 2
123--------------4
123--------------6
453--------------2
898--------------2
723--------------2
723--------------4

How to do insert or update increment of 2 in a column (col2) for every same value in the Col1?

I am using Access. Is it possible to with the query ?

View 2 Replies View Related

Insert Into Two Tables, Both With Auto Increment Fields

Jul 20, 2005

Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!

View 1 Replies View Related

Transact SQL :: Key Increment Even On Failed Insert Into On One Column

May 28, 2015

The reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null.  Having null values would of contradicted the table where two columns "not null" are a requirement.

CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,

[code]...

View 2 Replies View Related

Transact SQL :: How To Update A Field And Insert Increment Count

May 28, 2015

I want to update a field and in this field insert a increment count, for example:

When I make, "Select * from Users order by User" displays:

User1  |  NULL
User1  |  NULL
User1  |  NULL
User2  |  NULL
User2  |  NULL

and I want to do this:

User1  |  1
User1  |  2
User1  |  3
User2  |  1
User2  |  2

how to do this?

View 7 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

Need To Have 2 Auto Increment Columns (Seed, Increment)

Dec 11, 2007

Hello,

I Have a table that needs to have 2 unique number.

detail_id and detail_print_id.

detail_id is already an IDENTITY.

both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).

So I thought i could do the following:

detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah

that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.

is there anyway I can create a int column to auto increment, without the column being an IDENTITY??

also, I would prefer to not have to create a second table with a single column just for this work.

Thanks,
Justin

View 5 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

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

How To Insert Int Variable...

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

Variable To Insert Parameter

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

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

Insert SP Results Into Variable?

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

INSERT INTO Table(@variable) V...

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

Insert Local Variable Value

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

Accessing The Return Variable Of An Insert

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

Insert A Variable Into HTML Body?

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

Insert Values Of All The Fields Into Variable?

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

Help On Global Variable For Insert Statement

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

How To Insert SSIS Variable To OLE DB Destination

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

Auto Insert A Variable Number Of Records?

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

Howto Pass Variable Into Bulk Insert In SP?

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

Variable On Open Query - Insert Statement

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

Cannot Insert A Variable In SSIS Package Designer

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

Need Help Using Bulk Insert From A CSV With A Variable Number Of Fields

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

How Can You Use A Variable Tablename And Retrieve The Output From The Insert?

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

Sql Insert, Capture Scope_Identity Output To Session Variable?

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

BULK Insert From A Text File Name Stored Into A Variable

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

Integration Services :: INSERT Variable Values Into Table In SSIS

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







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