Test data update

Just curious if this is the best way of updating the balances? It executes fine but I am new to SQL Thanks for any advice...

*/
CREATE DATABASE KFF
GO

USE KFF

CREATE TABLE COA
(
Account INT NOT NULL,
Description VARCHAR(250),
Short_Description VARCHAR(250),
)

BULK INSERT COA FROM 'C:\COA-IMPORT.TXT' WITH
(
FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n' 
)

ALTER TABLE COA
ADD Balance money;


SELECT * FROM COA

use KFF
update COA S开发者_JAVA百科ET Balance=$1000 WHERE Account=100001
update COA SET Balance=$2000 WHERE Account=100002
update COA SET Balance=$3000 WHERE Account=100003
update COA SET Balance=$4000 WHERE Account=100010
update COA SET Balance=$5000 WHERE Account=100011
update COA SET Balance=$6000 WHERE Account=100012
update COA SET Balance=$7000 WHERE Account=100020
update COA SET Balance=$8000 WHERE Account=100021

I would have to say no, it's probably not the best way.

Could you please provide a fuller statement of your problem in terms of the business domain?

I can see that you are importing a chart of accounts from somewhere which is provided in CSV form.

Somehow your script knows the balances which should be assigned to the accounts, but it's not clear to me where this extra information is really coming from.

If you have another table with the balances (Balances), it seems that there would be no need to add a column to the imported data, you could simply combine the data after the chart of accounts is imported (presumably to get the latest names):

SELECT *
FROM Balances
INNER JOIN COA
    ON COA.Account = Balances.Account

or perhaps (if you aren't sure whether items could be missing on either side):

SELECT *
FROM Balances
FULL OUTER JOIN COA
    ON COA.Account = Balances.Account