SQL query statement for copying data from different rows to different rows

  • I have a table (dbo.ACCOUNT_INF) with fields company_code (secondary key), accounts_code (unique) and other columns.

    I like to copy rows containing a specific company_code and from accounts_code = 1 which is unique to accounts_code 100 along with full records (rows)

    to a another company_code starting from a specific accounts_code in the same table dbo.ACCOUNTS_INF

  • Can you clarify the scenario with some sample data?

  • zahid_7777 (10/8/2012)


    I have a table (dbo.ACCOUNT_INF) with fields company_code (secondary key), accounts_code (unique) and other columns.

    I like to copy rows containing a specific company_code and from accounts_code = 1 which is unique to accounts_code 100 along with full records (rows)

    to a another company_code starting from a specific accounts_code in the same table dbo.ACCOUNTS_INF

    Post a SELECT query which returns the rows you want to clone, we'll show you how to do the rest.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Please attach a screenshot of sample data or post query which you wrote so that we can do modification in that and give you desired result,

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • SELECT *, [COMCOD]

    ,[ACTCODE]

    ,[ACTDESC]

    ,[ACTELEV]

    ,[ACTTYPE]

    ,[ACTTDESC]

    ,[USERCODE]

    FROM [ASTREALERPDBR].[dbo].[ACINF]

    WHERE [COMCOD]=3306 OR [COMCOD]=3307

    Result's are (small portion),

    COMCODACTCODEACTDESCACTELEVACTTYPEACTTDESCUSERCODECOMCODACTCODEACTDESC

    3306810100010002Other's Income23306810100010002Other's Income

    3306810100010003Associaltion Fee (Sales)3306810100010003Associaltion Fee (Sales)

    3306810100010004Discount Received From Flat Owner3306810100010004Discount Received From Flat Owner

    3307110000000000Non Current Assets3307110000000000Non Current Assets

    3307110100000000Fixed Assets3307110100000000Fixed Assets

    3307110100010000Fixed Assets3307110100010000Fixed Assets

    3307110100010001Furniture & Fixture201010013307110100010001Furniture & Fixture

    3307110100010002Telecommunication201010013307110100010002Telecommunication

    3307110100010003Office Equipment201010013307110100010003Office Equipment

  • You need to replace the SELECT * with actual column names.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I just like to know the command to copy data (specific range like 10 rows) containing in a COMPANY_CODE and will be copied to another COMPANY_CODE starting with a new accounts_code which will be different from the previous accounts_code in a same dbo.ACCOUNT_INFO table.

    Structure of the table like

    COMPANY_CODE ACCOUNTS_CODE ACCOUNTS DESCRIPTION

    0001 18-01-0001-001 AR-SALEs ASSETS

    0001 18-01-0001-002 AR-X ASSETS

    0002 18-01-0001-001 AR-y ASSETS

    0002 18-01-0001-002 AR-y ASSETS

    last two record should be coppied to the company code 0001 starting from a defined account code and continue to add with by incremented by in the accounts code.

  • zahid_7777 (10/8/2012)


    I just like to know the command to copy data (specific range like 10 rows) containing in a COMPANY_CODE and will be copied to another COMPANY_CODE starting with a new accounts_code which will be different from the previous accounts_code in a same dbo.ACCOUNT_INFO table.

    Structure of the table like

    COMPANY_CODE ACCOUNTS_CODE ACCOUNTS DESCRIPTION

    0001 18-01-0001-001 AR-SALEs ASSETS

    0001 18-01-0001-002 AR-X ASSETS

    0002 18-01-0001-001 AR-y ASSETS

    0002 18-01-0001-002 AR-y ASSETS

    last two record should be coppied to the company code 0001 starting from a defined account code and continue to add with by incremented by in the accounts code.

    -- please provide us with the business rules

    -- for generating the new account codes

    INSERT INTO MyTable (

    COMPANY_CODE,

    ACCOUNTS_CODE,

    [ACCOUNTS DESCRIPTION])

    SELECT

    COMPANY_CODE = '0001',

    ACCOUNTS_CODE = ?, <<====

    [ACCOUNTS DESCRIPTION]

    FROM MyTable

    WHERE COMPANY_CODE = '0002'

    AND ACCOUNTS_CODE IN ('18-01-0001-001','18-01-0001-002')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (10/9/2012)


    zahid_7777 (10/8/2012)


    I just like to know the command to copy data (specific range like 10 rows) containing in a COMPANY_CODE and will be copied to another COMPANY_CODE starting with a new accounts_code which will be different from the previous accounts_code in a same dbo.ACCOUNT_INFO table.

    I need to specify starting account_code and continued to the last data sequentially.

    Structure of the table like

    COMPANY_CODE ACCOUNTS_CODE ACCOUNTS DESCRIPTION

    0001 18-01-0001-001 AR-SALEs ASSETS

    0001 18-01-0001-002 AR-X ASSETS

    0002 18-01-0001-001 AR-y ASSETS

    0002 18-01-0001-002 AR-y ASSETS

    last two record should be coppied to the company code 0001 starting from a defined account code and continue to add with by incremented by in the accounts code.

    -- please provide us with the business rules

    -- for generating the new account codes

    INSERT INTO MyTable (

    COMPANY_CODE,

    ACCOUNTS_CODE,

    [ACCOUNTS DESCRIPTION])

    SELECT

    COMPANY_CODE = '0001',

    ACCOUNTS_CODE = ?, <<====

    [ACCOUNTS DESCRIPTION]

    FROM MyTable

    WHERE COMPANY_CODE = '0002'

    AND ACCOUNTS_CODE IN ('18-01-0001-001','18-01-0001-002')

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

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