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