INSERT Multiple Rows into Multiple Tables From One Table 3

[ousoonerjoe]

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.

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.

 

[SQLDenis]

this can be done in a set based operation

you would split it up, first insert everything into Entities

then you join Entities with ClaimSetup on PrimaryID or whatever the key is
you would select the EntityID from Entities and the columns you need from ClaimSetup to insert everything into Address

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ousoonerjoe]

I'm not so sure that would work. Each table has an ID that references back to the other (Entities = EntityID, Address = AddressID). I'm so sure the logic you suggest would work with out able to pull the EntityID as it is being inserted and then inserting the Address line with the EntityID. Once I have the EntityID inserted into the address record, I can do a join and update the Entities table with the AddressID as needed, but the EntityID will need to be present to do the join to make the update. There is nothing in ClaimSetup that will bind to a unique value in Entities or Address. Why i was looking for a way to basically Loop through a SELECT FROM ClaimSetup to INSERT into Entities and Address based on the logic above.

Here are the Table Layouts:

CREATE TABLE [Address] (
	[AddressID] [int] IDENTITY (1, 1) NOT NULL ,
	[EntityID] [int] NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Street1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Street2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Zip] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_Address_1__13] PRIMARY KEY  NONCLUSTERED 
	(
		[AddressID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Entities] (
	[EntityID] [int] IDENTITY (1, 1) NOT NULL ,
	[PrimaryID] [int] NULL ,
	[Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Type] [tinyint] NOT NULL ,
	[Cooperator] [bit] NOT NULL ,
	[CoopChngDate] [smalldatetime] NULL ,
	[ProfContact] [bit] NOT NULL ,
	[TeamID] [int] NULL ,
	[SpecialistID] [int] NULL ,
	[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MailingAddressID] [int] NULL ,
	[DeliveryAddressID] [int] NULL ,
	[Created] [smalldatetime] NULL ,
	[LastModified] [smalldatetime] NULL ,
	[CreatedByID] [int] NULL ,
	[LastModifiedByID] [int] NULL ,
	[Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Role] [int] NULL ,
	[Inactive] [int] NULL ,
	CONSTRAINT [PK_Entities_1__23] PRIMARY KEY  CLUSTERED 
	(
		[EntityID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Any further thoughts or am i just not following the logic of your suggestion here?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

Do you have a natural key on the information? Can any of this information be duplicated inthe insert or if you join on all the fields will you get a unique record?

(BTW never use @@identity it will nto always give you the value you want. If a trigger is ever put onthe table that inserts to another table with an identity, you get that one not the one you wanted. Use scope_identity() instead inthe future.)

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

P.S. It is strongly recommended to avoid "Entity" and "Entities" for entity names in databases. For reasons explempified by that sentence: entity already has a meaning. By using "Entity" you start making people ask, which entity, the entity prototype entity or the entity instantiation entity? The replacement generic term that's industry practice is "Party.

 

[ousoonerjoe]

Unfortunately this is an inherited database. I had no control over the design and creation. There are many things about it i would have done differently.

SQLSister- I do have an ID field in ClaimSetup i can place in the description/notes field on insert to create a value to join to. Unfortunately, i'm trying to insert Names and Addresses, so they are not always unique.

I know how to do this in VB, was hoping this was possible in just SQL.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[fredericofonseca]

Another important aspect that may help you.

Are there other constraints on the tables? specially looking to see if EntityID on the addresses table has one for example. Others would also be required to know.

Reason behind this is that you may do it still with set based operations provided you either add a new field to some of the tables, or you use PrimaryID from entities (as it seems to be available = 0 on insert) as a way to link from your ClaimSetup into the entities, and hence to the other tables.

ClaimSetup would need to have a identity field you would use to link into the other tables, at least during insert.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ousoonerjoe]

Looks like i can use the PrimaryID in Entities and populate it with [ID] from ClaimSetup to create a 1 to 1 matching. I can just clear it out when i'm done.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ousoonerjoe]

Thank you all for the help. Each piece of info finally got me there just in time to learn the contract for the client fell through. At least i learned something i can take to the next project...

thank you all for the assist.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.