mercredi 29 juin 2016

SQL - Split One Table to Two And Link

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();

Aucun commentaire:

Enregistrer un commentaire