September 18, 2013 at 11:24 am
Hello all.
First of all, I wanted to thank everyone for participating in these forums and helping those who need help. Like me! I know you're taking time out of your day to help others. Sometimes, you just can't get the answer from a book or more importantly, some feedback!
I am writing a SP which will take data from an old table (tens of thousands of records) and insert it into two related tables. I'm still early int he writing and testing phase so the code below is an early draft and a mock up.
I have a table which contains basic customer information and account numbers. As a precautionary measure, I plan to take the account numbers out of the new table and store them in a separate table. So my old table may look like this:
OldCustomers
-------------
OldCustomerID
OldAccountNum
FullName
Address
And my two new tables may look like this:
NewCustomers
------------------
NewCustomerID
AccountID (FK)
FullName
Address
Accounts
----------------
AccountID
AccountNumber
So my LOOP logic is to:
*Grab the first Account # from OldCustomers and insert into the Accounts Table.
*Grab the ID from Accounts that was just created.
*Insert the rest of the record into NewCustomers and take that ID and insert it as the FK.
*Go to next record.
So I have written code like this:
[font="Courier New"]--INSERT Account Nums into ACCOUNTS Table First
INSERT INTO ACCOUNTS
([AccountNumber],[Active])
VALUES
((
--Pull fron OldCustomers and get Account #. If empty (''), insert all zeros.
CASE
WHEN (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) LIKE '' THEN
'000000000'
-- Insert Account # from OldCustomers
ELSE
(SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)
END
),1)
--INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.
INSERT INTO NewCustomers
([AccountID],[FullName],[Address])
(
SELECT (SELECT IDENT_CURRENT('ACCOUNTS')),[FullName],[Address]
FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)
END[/font]
In case you're wondering, the BETWEEN 1 and 5 clause is just for testing. Don't want to insert 75,000 records on a test.
When I try to execute this, SQL Server barks at me:
[font="Courier New"]sg 512, Level 16, State 1, Procedure sp_Insert...... Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_Insert........, Line 40
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_Accounts". The conflict occurred in database "MyDatabase", table "dbo.Accounts", column 'AccountID'.
The statement has been terminated.[/font]
I'm going to assume that both errors are coming from my lack of a loop. I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.
Correct?
Some guidance would be appreciated!!
September 18, 2013 at 11:35 am
I am not sure I follow your logic in this and I would think about the process flow. I would argue that if this is to unltimatley load 75,000 records you should not be doing and insert values command. Either way your probalem is the embbedded selcect returns more than one row which in not allow in the case statement.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 11:41 am
Thanks. Volume isn't an issue as this will be a one time load. But my logic is certainly questionable! =)
Logic is simple.
Take records from old table and insert them into two new tables which have a relationship.
Any help?
September 18, 2013 at 11:59 am
my recommendation would be to get a select working to correctly display the information as you will ant in to be displayed in your new table. Then just add and insert statement infront of that select.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 2:15 pm
What is the relationship? Can a customer have multiple accounts or can an account have multiple customers.
>> I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.
Yes, whatever statement you run first will occur first.
The order or the statements could matter if you have a foreign key relationship created (and you really should).
September 18, 2013 at 2:27 pm
After much tinkering and research, I think I have a prototype that works. Ended up using a cursor and parameters. It's not bulletproof but so far, this works as expected:
[font="Courier New"]USE AcmeCars;
GO
DECLARE @OldAccountNum varchar(10), @FullName varchar(50), @Address varchar(50), @AccountID int;
DECLARE CustomerCursor CURSOR FOR
SELECT OldAccountNum, FullName, Address FROM OldCustomers
OPEN CustomerCursor;
-- Perform the first fetch.
FETCH NEXT FROM CustomerCursor
INTO @OldAccountNum, @FullName, @Address;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the Account # from the old table into the new.
INSERT INTO Accounts([AccountNum], [Active]) VALUES (@OldAccountNum, 1)
--Get the ID
SET @AccountID = (SELECT IDENT_CURRENT('ACCOUNTS'))
--INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.
INSERT INTO NewCustomers([AccountID],[FullName],[Address])
VALUES
(@AccountID, @FullName, @Address)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM CustomerCursor
INTO @OldAccountNum, @FullName, @Address;
END
CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;
GO
[/font]
September 18, 2013 at 3:23 pm
Am I missing something or would something like this will work correctly and much faster than the cursor?
CREATE TABLE #OldCustomers(
OldCustomerID int,
OldAccountNum varchar(100),
FullName varchar(100),
Addressvarchar(100))
CREATE TABLE #NewCustomers(
NewCustomerID int,
AccountID int,
FullName varchar(100),
Addressvarchar(100))
CREATE TABLE #Accounts(
AccountID int IDENTITY,
AccountNumber varchar(100))
INSERT INTO #Accounts( AccountNumber)
SELECT DISTINCT OldAccountNum
FROM #OldCustomers
INSERT INTO #NewCustomers(
NewCustomerID,
AccountID,
FullName,
Address)
SELECT OldCustomerID,
AccountID,
FullName,
Address
FROM #OldCustomers c
JOIN #Accounts a ON c.OldAccountNum = a.AccountNumber
If you read the article linked on my signature, you can get even better help.:-)
September 18, 2013 at 3:24 pm
I know you posted a solution that works for you, but if you could humor me. . .
Could you try the following code? I am interested to know if it works. If it does, there should not be an issue with speed. It should insert 75,000 record in under a couple of seconds. Since DLL was not posted, the code is assuming that an insert into Accounts has an Identity field on AccountID that gets incremented.
INSERT Accounts (AccountNumber)
(SELECT DISTINCT OldAccountNum
FROM OldCustomers)
)
INSERT NewCustomers (CustomerID, AccountID, FullName)
(SELECT O.OldCustomerID, A.AccountID, O.FullName
FROM OldCustomers O
JOIN Accounts A on A.AccountNumber = O.OldAccountNum
)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2013 at 3:29 pm
Luis Cazares (9/18/2013)
Am I missing something or would something like this will work correctly and much faster than the cursor?
LinksUp (9/18/2013)
. . If it does, there should not be an issue with speed. It should insert 75,000 record in under a couple of seconds.
Wow, talk about being on the same page and just a minute apart!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2013 at 4:08 pm
I'll be glad to try these samples tomorrow. I'll post results. Always willing to learn and improve....
September 19, 2013 at 7:33 am
Thanks guys. Both solutions were almost identical and I was able to get them both to work. It seems I was over complicating the process. Thanks again!
September 19, 2013 at 9:14 am
I'm glad that we could help.
After a while we all start looking for the simplest way to do it. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply