SQL - Split One Table to Two And Link

Question

I am using SQL Server 2014. I looked up solution by Joel Coehoorn for this question but it did not work for me.

I have a table for clients which I renamed to Aac_Client which has both client information and address information as columns. I want to move Address to a new table Address to organise things and keep other remaining things at Ac_Client. I am trying to do the following:

Note the relationship between Address and Client is One to One.

BEGIN TRANSACTION
   DECLARE @DataID int;

   -- Insert Address
    INSERT INTO Address ([StreetNumber],[StreetName] ,[StreetAddress2] ,[Unit] ,[City] ,[State] ,[Zip] ,[County])
      SELECT  [StreetNumber], [StreetName], [StreetAddress2], [Unit] ,[City] ,[State] ,[Zip] ,
      NULL AS [County]
      FROM Aac_Client

    -- Get Address Id
   SELECT @DataID = scope_identity();

    -- Insert Client    
    INSERT INTO Ac_Client ( AddressId, Name, Phone, Contact )
        SELECT  @DataID AS AddressId, Name , Phone, Contact FROM Aac_Client

COMMIT

But the problem is INSERT is carried at once and I get the last ID of address in SELECT @DataID = scope_identity();

score 2 · Answer 1 · answered Jun 26 '16 at 21:51

2

BEGIN TRANSACTION

    IF OBJECT_ID('tempdb..#InsertedAddresses') IS NOT NULL
    BEGIN
        DROP TABLE #InsertedAddresses
    END

CREATE TABLE #InsertedAddresses (
    AddressId INT
    ,ClientId INT
)

   DECLARE @DataID int;

   -- add a unique client identifier
   -- Insert Address
    INSERT INTO Address ([StreetNumber],[StreetName] ,[StreetAddress2] ,[Unit] ,[City] ,[State] ,[Zip] ,[County], [ClientId])
    OUTPUT INSERTED.AddressId, INSERTED.ClientId (AddressId, ClientId)
      SELECT  [StreetNumber], [StreetName], [StreetAddress2], [Unit] ,[City] ,[State] ,[Zip] ,
      NULL AS [County]
      FROM Aac_Client

    -- Insert Client    
    INSERT INTO Ac_Client ( AddressId, Name, Phone, Contact, ClientId)
        SELECT i.AddressId, Name , Phone, Contact, c.ClientId
          FROM Aac_Client c
          INNER JON #InsertedAddresses i
          ON c.ClientId = i.ClientId

COMMIT

use the output clause of the first insert statement into a temp table. Also is it possible that clients will have more than one address? Or more then 1 client at the same address? If so you might want to consider putting the AC_Client ID in the address table instead of the address_id in the client table. Also if you are not worried about changing addresses independently in the case of multiple clients at same address you could consider a 3rd table to relate addresses and clients so you don't have to repeat addresses or clients to create a many to many relationship.

I guess to expand scope_identity is a scalar value meaning only 1 value is held and it is the identity of the last row altered in the scope. In your case you need all of the ids so you need to use the output clause.

answered Jun 26 '16 at 21:51

Matt

13,833
2
16
28

Your `Address` insert statement assumes there is a `ClientID` column in the `Address` table, which obviously would have made things much simpler, but this isn't the case hence answer would not help the OP. – M.Ali Jun 26 '16 at 22:07
yeah I know there has to be one or this is no relation back to his client table. Well I guess that is assuming AddressId is an IDENTITY column on his new address table because it wouldn't be likely to exist in his current client table – Matt Jun 26 '16 at 22:12
I think it is the `AddressID` column that will link back to the Client Table since the Client table has the AddressID column. – M.Ali Jun 26 '16 at 22:15
Yeah eventually but to link back the temp table or variable of the output cluase you need something for that relationship. you are suggesting altering his table and adding a NewID(). I was simply trying to tell him to use the identity in the table if one exists. We are saying essentially the same thing again. Although lets hope it's not a lot of data if using a table vairable – Matt Jun 26 '16 at 22:21
I have suggested altering table and adding columns only for this Data split purpose (as it is needed) once the job is done drop the columns, not suggesting a schema change at all, but your solution assume things that are not there hence suggesting a schema change as a solution "which is unnecessary by the way" :) ........... – M.Ali Jun 26 '16 at 22:24
There is obviously more to his schema than he wrote so a unique id may exist. I actually do suggest data schema changes because he is forcing a one to one relationship rather than a one to many or a many to many on data that typically should be help at least one to many. – Matt Jun 26 '16 at 22:27
Don't know the business logic of the application so I wouldn't comment on that but yes usually one person can/may have more than one address. – M.Ali Jun 26 '16 at 22:30
@M.Ali it's a One to One relationship. At least for now. – tika Jun 26 '16 at 22:45
1

@tika I rejected your potential edit because the thing is you have to have a way of relating data from the output table to your client table. If you don't have a clientid or id on your aaC_client table that you can use then you will have to use M.Ali's or a similar method of creating one at least temporarily. Also in order to get inserted.clientid you actually have to insert it. You can always drop the column from the address table after – Matt Jun 26 '16 at 23:24
@Matt Cool. I will figure it out. – tika Jun 26 '16 at 23:27

M.Ali · Accepted Answer · 2016-06-26T22:02:50.383

--Add the following Columns that you can later Drop 

ALTER TABLE [Address] ADD Col_GUID UNIQUEIDENTIFIER;
ALTER TABLE [Aac_Client] ADD Col_GUID UNIQUEIDENTIFIER;
GO

-- Give a Unique value to each row.
UPDATE [Aac_Client] SET Col_GUID = NEWID();

BEGIN TRANSACTION;

   -- Table variable to capture newly generated Identity values
   Declare @NewID TABLE (AddressId INT, Col_GUID UNIQUEIDENTIFIER) 

   -- Insert Address (with output clause to get the Identity values)
INSERT INTO [Address] ([StreetNumber],[StreetName] ,[StreetAddress2] 
                                      ,[Unit] ,[City] ,[State] ,[Zip] ,[County], Col_GUID)
OUTPUT Inserted.AddressId , Inserted.Col_GUID INTO @NewID (AddressId , Col_GUID)
SELECT  [StreetNumber], [StreetName], [StreetAddress2] 
                            ,[Unit] ,[City] ,[State] ,[Zip] , NULL AS [County] , Col_GUID
FROM Aac_Client


    -- Insert Client joining with the Table variable on the guid column to
    -- to get the new Identity values. 

INSERT INTO Ac_Client ( AddressId, Name, Phone, Contact )
SELECT  N.AddressId, a.Name , a.Phone, a.Contact 
FROM Aac_Client a
INNER JOIN @NewID N ON n.Col_GUID = a.Col_GUID

COMMIT TRANSACTION;

Finally you can drop the Columns you added for this purpose.

ALTER TABLE [Address] DROP COLUMN Col_GUID;
ALTER TABLE [Aac_Client] DROP COLUMN Col_GUID;

SQL - Split One Table to Two And Link

2 Answers2