ousoonerjoe
Programmer
I have a flat file I'm importing into our MS SQL2000 database. I have loaded the flat file into a table (ClaimSetup) and need to insert certain elements of that data into the main tables. There are 2 main destination tables (Entities, Address... there are more, but will keep it simple for now). Both Tables have ID fields that reference back to each other.
Shown is a sample of a single INSERT that gets the ID's and places them in the appropriate fields. The question is, How does one loop through a SELECT statement to place these values into the INSERT statements? I'd rather do this in SQL than write a program that loops through a RecordSet and does the INSERTs and UPDATEs if at all possible. As always, any thoughts, comments, or suggestions are appreciated.
"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.
Shown is a sample of a single INSERT that gets the ID's and places them in the appropriate fields. The question is, How does one loop through a SELECT statement to place these values into the INSERT statements? I'd rather do this in SQL than write a program that loops through a RecordSet and does the INSERTs and UPDATEs if at all possible. As always, any thoughts, comments, or suggestions are appreciated.
Code:
DECLARE @AddID INTEGER
DECLARE @EntID INTEGER
INSERT INTO Entities (PrimaryID, Name, FName, MName, LName, Type, Cooperator, CoopChngDate,
ProfContact, TeamID, SpecialistID, Notes, MailingAddressID, DeliveryAddressID, Code,
Role, Flag1099, NotRealAdjustor, Inactive)
VALUES (0, 'Bush, George', 'George', NULL, 'George', 2, 0, GETDATE(),
0, NULL, NULL, 'IMPORTED FROM GB', 0, NULL, '',
4, NULL, 0, NULL)
SET @EntID = (SELECT @@IDENTITY)
INSERT INTO Address(EntityID, Description, Street1, Street2, City, State, Zip, Country)
VALUES (@EntID, 'IMPORTED FROM GB', '300 Penn ST.', '', 'Washington', 'DC', '00000-0000', 'USA')
SET @AddID = (SELECT @@IDENTITY)
UPDATE Entities
SET MailingAddressID = @AddID
WHERE EntityID = @EntID
SELECT * FROM Entities WHERE EntityID = @EntID
SELECT * FROM Address WHERE AddressID = @AddID
"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.