Insert into parent table and child tables

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]

Answers


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

Need Your Help

How to use the json?

javascript html json

I have called an api using href from the html form and it in response gives json as output.

What's the "right" way to organize GUI code?

matlab user-interface matlab-guide matlab-deployment

I'm working on a fairly sophisticated GUI program to be deployed with MATLAB Compiler. (There are good reasons MATLAB is being used to build this GUI, that is not the point of this question. I real...