Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    156

    Unanswered: Create table help

    Hi there,

    i'd like to create a table from other tables. My new table will only have two columns. Let's call the new table 'C', and it's created by a Select on 'A' and 'B'

    A.control_id and B.account_id

    one control can have many accounts, ok. However, I'd like to create 'C' with the control_id and a concatenation of all associated account_id's.

    eg:

    control_id ¦ linked_accounts
    --------------------------------
    1 ¦ account_id1, account_id2, account_id3

    Any help would be appreciated.

    Regards,
    rgs,

    Ghostman

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Have you looked into the "INSERT INTO" statement?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Sep 2003
    Posts
    156
    It's the concatenation that's getting me. I was doing this with a function, but as soon as the report I'm creating references the function, performance goes pear-shaped.

    I was doing this...

    BEGIN
    DECLARE @RowCnt int
    DECLARE @MaxRows int
    DECLARE @linkedaccounts varchar(256)
    DECLARE @global_control_id int
    DECLARE @accountstr varchar (1000)

    SELECT @RowCnt = 1
    SET @accountstr=''

    DECLARE @Import TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL,
    Control_id int,global_control_id int, Title varchar(256) )

    INSERT INTO @Import
    SELECT DISTINCT dbo.NTH_RPT_CONTROLS.CONTROL_ID, dbo.NTH_RPT_CONTROLS.GLOBAL_CONTROL_ID, dbo.NTH_RPT_ACCOUNT.TITLE
    FROM dbo.NTH_RPT_ACCOUNT_CONTROL INNER JOIN dbo.NTH_RPT_ACCOUNT ON dbo.NTH_RPT_ACCOUNT_CONTROL.ACCOUNT_ID=dbo.NTH_RPT _ACCOUNT.ACCOUNT_ID
    AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_A CCOUNT.LABEL_ID
    INNER JOIN dbo.NTH_RPT_CONTROLS
    ON dbo.NTH_RPT_ACCOUNT_CONTROL.CONTROL_ID=dbo.NTH_RPT _CONTROLS.CONTROL_ID
    AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_C ONTROLS.LABEL_ID
    WHERE dbo.NTH_RPT_CONTROLS.CONTROL_DID = @control_did

    SELECT @MaxRows=count(*) from @Import

    WHILE @RowCnt <= @MaxRows

    BEGIN

    SELECT @linkedaccounts = title, @global_control_id = global_control_id
    FROM @Import
    WHERE rownum = @RowCnt

    SELECT @RowCnt = @RowCnt + 1
    SET @accountstr = @accountstr + @linkedaccounts +', '

    END
    RETURN @accountstr
    END

    ----------------------------------
    then i created a view...
    ----------------------------------

    CREATE VIEW dbo.VIEW_LINKED_ACCOUNTS
    AS
    SELECT control_did, dbo.f_linked_accounts(dbo.nth_rpt_controls.control _did) AS account_names
    FROM dbo.nth_rpt_controls

    ----------------------------------


    But performance is bad. so i'd like to create a permanent table that is updated by a trigger on the underlying tables.
    rgs,

    Ghostman

  4. #4
    Join Date
    Sep 2003
    Posts
    156

    keep the function

    Iäm thinking keeop the function, it's still useful - but create my new table and use a trigger to execute the function and in the function send the concatenated string to the new table.
    rgs,

    Ghostman

Posting Permissions

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