Database

INSERT INTO student (Ch1, ch2, ch3)<br />
SELECT ch1, 123 AS ch2, 124 AS ch3<br />
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',<br />
'Excel 8.0;Database=C:\test\xltest.xls', [Feuil1$])

USE takhlees

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DELETE FROM accounts_summary

-- reset primary key

INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')

DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers