Post reply

Creating new transactions

  • June 14, 2007 at 12:56 pm

    #91111

    Hi All,

    I think my first post was a little confusing, so I will scratch that and try to ask this as simply as I can.

    I have a database table called transactions. In this table, there are transactions that were made that charged a 'deposit' to reservation accounts. The table has several columns, inluding keyID reftransaction, etc.

    I need create a new transaction for each instance, (there are over 1000 transactions) that will create a credit to the deposit charge for all 1000. Thus creating 1000 new transactions. I know how to do this one by one, but I was wondering, how can I credit to each transaction all at one time so I can credit to the deposit that was charged for each account?

    Thanks.

  • June 14, 2007 at 1:05 pm

    #713039

    If you post a sample table and sample data someone could easily help.

    How are you inserting the records one at a time?

    If you want to create 1000 new records using the data from the transaciton table.

    Rough example using Insert Into Select

    Insert into Transactions (KeyID, RefTransaction...)

    Select KeyID, RefTransaction, ......

    From Transactions

    Where transactiontype = 'Deposit'

    http://msdn2.microsoft.com/en-us/library/h54fa37c(VS.80).aspx

  • June 14, 2007 at 1:12 pm

    #713040

    Ray thankyou, I am going to try that out, and if I am still running into problems I will let you know. Thanks!

  • June 14, 2007 at 4:48 pm

    #713072

    Okay, the INSERT command answers my question.

    Now my question is: I have one column that contains a -25.00 and 25.00, and maybe a few have different values. If I was to select something in a column with only a negative number, how would I do that, thanks.

  • June 14, 2007 at 4:52 pm

    #713073

    declare @Foo table (pk int identity,

                         bar int)

    insert into @Foo (bar)

    select 1 union all

    select 2 union all

    select 0 union all

    select -1 union all

    select -4

    select *

    from @Foo

    where bar <0

    Results:

    4, -1

    5, -4

  • June 15, 2007 at 2:20 pm

    #713398

    Ray,

    Thanks...now here is my next situation.

    I have a table with the following columns: Rtype, Amount, Refnumber, Color:

    Here is what the table looks like:

    RD 25 1blue

    AB 45 2green

    AC 25 3purple

    AD 34 4blue

    AE 25 5pink

    Now lets say I would like to create new transactions for all 'RD' Rtypes, but I would like to change the Amount to lets say, 99 for all of 'RD' types, but keep the same refNumber. For example, I want to create and insert a new transaction for RD, but with the amount 99, so that the row looks like this:

    RD 99 1 blue

    Where do I put in the query to change the Amount value? The query I am using, but not working is:

    INSERT INTO EMPLOYEES(Rtype, Amount, Refnumber, Color)

    SELECT Rtype, Amount, Refnumber, Color

    FROM Employees

    WHERE Rtype = 'RD';

  • June 15, 2007 at 3:20 pm

    #713406

    Please don't cross post. I just answered your question in your other thread.

    -SQLBill

  • June 15, 2007 at 3:34 pm

    #713411

    SQL Bill, sorry about that....

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply