Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: specifying table name programmatically

    Hi...
    I want to specify the table name pprogrammatically within SQL command. Like

    insert into "Sales_" + month(Temp.TransDate) FROM Temp WHERE
    ID NOT IN (SELECT ID FROM "Sales_" + month(Temp.TransDate))

    Or is there a way to transfer imported data which is consists of mixed month into their corresponding tables which is divided into months? Such as month 11 sales from temp goes to Sales_11 and Month 10 Sales goes to Sales_10.

    How I can calculate the transaction programmatically inside SQL?
    Say... I have 3 kind of tables
    1. Transaction list: TBName (string), Desc (string), Debet (bit)
    2. Transaction tables: consists of all tables at table number 1.
    3. Stock table.

    i.e.
    Table 1:
    Sales, Sales, Debet
    RMA, RMA to Head, Debet
    Ret, Returned, Credit
    Trans, Transfer, Credit
    Pro, Promotion, Debet

    Also I have every tables listed at field 1 above, and also stock table which holds stock data calculated from tables above as Stock = Ret+Trans-Pro-RMA-Sales.
    The transaction list right of equal sign will be like:
    SELECT sum(Qty) FROM "(SELECT TBName FROM TransList)"

    Can someone help me create these queries? Thank you.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Look at sp_executesql in bol - this will handle your dynamic sql creation. How is the data being imported ? For the last part, what do you mean by "calculate the transaction" ?

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    Thx... I will check the command...
    I want to import the database from temporary, for example Sales records, and I need to divide the transaction at Temp table into several Sales tables according to their month.

    The calculation part, i mean I want to calculate the transaction based on another database.

    So, for example I have several table consist of

    TableData
    Table, Description, AddToStock
    Sales, Sales table, False
    HRMA, RMA Table to HeadCounter, False
    CRMA, RMA Table from Customer, True
    Receive, Received Item, True

    Sales
    Product, qty
    Item 1, 12

    HRMA
    Product, qty
    Item 2, 1

    CRMA
    Product, qty
    Item 1, 2

    Receive
    Product, qty
    Item 1, 20
    Item 2, 50
    Item 3, 2

    Then Stock Table will contain
    Product, Qty
    Item 1, 10
    Item 2, 49
    Item 3, 2

    Stock table Qty result will be based on the TableData table, calculates every single table listed at TableData. The AddToStock column at Table Data means Add if set true, and subtract if set false.
    I need this code perform as a single function at SQL, called in a set of time, i.e. 1 hour, and everytime user press commit at client.
    I will call this function to transfer data from temp tables, according to their month and transaction, then recalculates updated Stock data according to TableData table. So, kind like batch processing.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    So basically you want to loop through the tabledata table and sum all the items from all the tables (subtracting where false and adding where true) - correct ?

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    yes....

    The procedure will be import everything from Temp tables into their respective tables, and perform calculation loop to update the data.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    I only advocate the use of cursors when REALLY necessary. That being said look at the following and tweak it to your specifications. Which version of sql are you running ?

    declare ctest cursor
    for
    select tablename, addto from table1
    declare @t1 varchar(20)
    declare @add char(1)
    declare @sql nvarchar(400)

    open ctest

    fetch next from ctest into @t1, @add
    if @add = 'T'
    begin
    select @sql = 'select item, qty into ##test from ' + @t1
    exec sp_executesql @sql
    end
    else
    begin
    select @sql = 'select item, (qty * -1) as qty into ##test from ' + @t1
    exec sp_executesql @sql
    end

    while @@fetch_status = 0
    begin
    fetch next from ctest into @t1, @add
    if @@fetch_status = 0
    begin
    if @add = 'T'
    begin
    select @sql = 'insert into ##test select item, qty from ' + @t1
    exec sp_executesql @sql
    end
    else
    begin
    select @sql = 'insert into ##test select item, (qty * -1) as qty from ' + @t1
    exec sp_executesql @sql
    end
    end
    end
    close ctest
    select item, sum(qty) from ##test group by item
    select * from ##test
    deallocate ctest

  7. #7
    Join Date
    Sep 2002
    Posts
    30
    wow... thx... I really need that..
    Don't worry, it only happen about 6 hours once..
    At lunch time, and midnight. So the impact will be minimal.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Your welcome and good luck.

  9. #9
    Join Date
    Aug 2002
    Posts
    2
    I have a similar stored proc., except that the select statement needs to be dynamically constructed. I normally use exec() to execute the statement, but seems like you cannot do that when using cursor. Any workarounds?

    ...

    DECLARE @Where2 varchar(800)
    SELECT @Where2 = (SELECT Criteria FROM tblQueryTemplatesDesc WHERE QueryID = @intQueryID)
    SET @Where = @Where + " AND " + @Where2


    DECLARE tnames_cursor CURSOR
    FOR
    EXEC (@Select + @From + @Where)
    OPEN tnames_cursor

    DECLARE @CID int

    FETCH NEXT FROM tnames_cursor INTO @CID
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    --Insert Statements Here
    END
    FETCH NEXT FROM tnames_cursor INTO @ListID
    END
    CLOSE tnames_cursor
    DEALLOCATE tnames_cursor

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Put the exec around the declare.

  11. #11
    Join Date
    Aug 2002
    Posts
    2
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •