SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Neel 7777
Neel 7777
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
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
Bhumika Jawanjal
Bhumika Jawanjal
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 33
Can you clarify the scenario with some sample data?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16714 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 2766
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/
Neel 7777
Neel 7777
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
SELECT *, [COMCOD]
,[ACTCODE]
,[ACTDESC]
,[ACTELEV]
,[ACTTYPE]
,[ACTTDESC]
,[USERCODE]
FROM [ASTREALERPDBR].[dbo].[ACINF]
WHERE [COMCOD]=3306 OR [COMCOD]=3307
Result's are (small portion),
COMCOD ACTCODE ACTDESC ACTELEV ACTTYPE ACTTDESC USERCODE COMCOD ACTCODE ACTDESC
3306 810100010002 Other's Income 2 3306 810100010002 Other's Income
3306 810100010003 Associaltion Fee (Sales) 3306 810100010003 Associaltion Fee (Sales)
3306 810100010004 Discount Received From Flat Owner 3306 810100010004 Discount Received From Flat Owner
3307 110000000000 Non Current Assets 3307 110000000000 Non Current Assets
3307 110100000000 Fixed Assets 3307 110100000000 Fixed Assets
3307 110100010000 Fixed Assets 3307 110100010000 Fixed Assets
3307 110100010001 Furniture & Fixture 2 0101001 3307 110100010001 Furniture & Fixture
3307 110100010002 Telecommunication 2 0101001 3307 110100010002 Telecommunication
3307 110100010003 Office Equipment 2 0101001 3307 110100010003 Office Equipment
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16714 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
Neel 7777
Neel 7777
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
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.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16714 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
Neel 7777
Neel 7777
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
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')


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search