Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query statement for copying data from different rows to different rows Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 1:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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
Post #1369644
Posted Monday, October 8, 2012 3:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 11:49 PM
Points: 30, Visits: 33
Can you clarify the scenario with some sample data?
Post #1369677
Posted Monday, October 8, 2012 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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
Post #1369678
Posted Monday, October 8, 2012 4:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1369712
Posted Monday, October 8, 2012 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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
Post #1369773
Posted Monday, October 8, 2012 7:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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
Post #1369792
Posted Monday, October 8, 2012 10:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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.
Post #1370157
Posted Tuesday, October 9, 2012 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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
Post #1370191
Posted Wednesday, October 10, 2012 3:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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')

Post #1370782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse