Insert into parent table and child tables

Question

My problem is with @officeident. In that for each new record inserted into LicenseHolder it has the same officeident.. this SQL inserts into OfficeID the value 1495 for every row. Which was the last identity inserted. Which does not create the parent child relationship.

I think I should be researching a for next cursor. For every insert into Office a new row is then added to LicenseHolder so I can build the relationship. Or if there is a simpler way or any help would be greatly appreciated.

DECLARE @officeident INT insert into [MembersDB].[dbo].[Office] ([AddressLine1] ,[AddressLine2] ,[State] ,[PostCode]) select [OfficeMailingAddr],[OfficeMailingAddr],[state],'1' FROM [Members].[dbo].[Main] SET @officeident = SCOPE_IDENTITY() INSERT INTO [MembersDB].[dbo].[LicenseHolder] ([Name] ,[Email] ,[Mobile] ,[OfficeNumber] ,[LicenseHolderTypeID] ,[PartyTypeID] ,[OfficeID]) SELECT [OfficeOf] ,[OfficeEmail] ,[Phone] ,'1234' ,'1' ,'1' ,@officeident --I want like this to be different for each row. FROM [Members].[dbo].[Main]

AlexT82 · Accepted Answer · 2013-12-09 23:36:59Z

Try something like this. I wrote it off the top of my head, so you might need to debug still:

Basically, i wrote a cursor here, which loops through the results of the table you want to use to insert into the new tables. And then create the 2 new insert records at once... (and then do the same with the next result line from the MAIN table).

DECLARE @OfficeMailingAddr as varchar(254), @OfficeMailingAddr as varchar(254), @state as varchar(254), @Name as varchar(254), @Email as varchar(254), @Mobile as varchar(254), @Phone as varchar(254) declare NewCursor Cursor for SELECT [OfficeMailingAddr],[OfficeMailingAddr],[state], [Name] ,[Email] ,[Mobile] ,[OfficeNumber] FROM [Members].[dbo].[Main] open NewCursor fetch next from NewCursor into @OfficeMailingAddr, @OfficeMailingAddr, @state, @Name, @Email, @Mobile, @Phone WHILE @@FETCH_STATUS = 0 begin insert into [MembersDB].[dbo].[Office] ([AddressLine1] ,[AddressLine2] ,[State] ,[PostCode]) VALUES (@OfficeMailingAddr, @OfficeMailingAddr, @state,'1') SET @officeident = SCOPE_IDENTITY() INSERT INTO [MembersDB].[dbo].[LicenseHolder] ([Name] ,[Email] ,[Mobile] ,[OfficeNumber] ,[LicenseHolderTypeID] ,[PartyTypeID] ,[OfficeID]) VALUES (@Name, @Email, @Mobile, @Phone,'1234' ,'1' ,'1' ,@officeident) FETCH NEXT FROM NewCursor INTO @OfficeMailingAddr, @OfficeMailingAddr, @state, @Name, @Email, @Mobile, @Phone END Close NewCursor deallocate NewCursor

Collectives™ on Stack Overflow

Insert into parent table and child tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related