need help inserting data into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help inserting data into a table

Author

Topic

maxjam
Starting Member

9 Posts

Posted - 2007-01-05 : 07:47:55

im selecting data from 2 tables, account and customers, into one table called MasterAccount.

i need to fill out a field called customer_number in MasterAccount which is used in the case where there is a joint account with two or more related customers.

This field needs to increment as i populate the table so for example, if there is a single account in the Account table with its ID = 'acc001' and there is just 1 related customer then there is one record created in MasterAccount table with account_field = acc001 and customer_number = 1.

if however there is a joint account 'acc001' and there are 3 related customers then there are three records created in MasterAccount table with the following format
account_field = acc001 and customer_number = 1
account_field = acc001 and customer_number = 2
account_field = acc001 and customer_number = 3

how do i do this given that my select is something like this

[CODE]
INSERT INTO MasterAccount
SELECT
AccountID AS Account_Number,
CustomerID AS Customer_Number,
FROM
AccountTable INNER JOIN CustomerTable ON
CustomerTable.customerAccountID = AccountTable.AccountID
[/CODE]

Your help is much appreciated!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 12:08:34

From you description it's not clear whether the customer number you're trying to get already exists in the customers table or not. If it does then just select it in your query, I assume it doesn't or you wouldn't be asking. So here is how you can generate a new incremental number by account number.

INSERT INTO MasterAccount
SELECT 
	AccountID AS Account_Number,
	ROW_NUMBER() OVER 
	(PARTITION BY AccountID ORDER BY CustomerID) AS Customer_Number,
FROM 
	AccountTable INNER JOIN CustomerTable ON 
	CustomerTable.customerAccountID = AccountTable.AccountID

This will only work on SQL Server 2005, but as you posted to the 2005 forum, I assume that's OK.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources