I really really want to use an INSERT nested in a SELECT

Question

I know this is a task that can't be unique, but I can't seem to find a way to do what I'm thinking in my searches and reading, so if it's not possible, I'd like to know if I'm choosing the best alternative.

Simple case of moving information on people from the old database to the new. The problem is that the old schema has the address on each row, and we are breaking it out into its own Address table for the new.

What I want to do is something like this:

INSERT INTO [newDB].[dbo].[Persons] ([FirstName], [LastName], [AddressId], [SSN])
SELECT
    p.[Firstname],
    p.[Lastname],
    AddressId =
    (
        -- The result of inserting the address into the
        -- table or the existing address' ID
    ),
    p.[SSN]
FROM [oldDB].[dbo].[tblPersons] p

Is there a way to do that which I am unable to find, or should I first get the addresses in and then match, or something I'm missing entirely? Any help would be appreciated, this is the first time I've had to work at bringing someone else's data forward and my unwarranted optimism won't hole out much longer. ;)

Answer 1

It sounds like what you're looking for is SELECT INTO

Answer 2

yes, you should first get the addresses in & then match to assign the ids when inserting the persons records.

i.e. you can't do an insert in the sub query.

Answer 3

I would try something using a table var for moving the data

DECLARE @OldTable TABLE(
     PersonID INT,
     FirstName VARCHAR(50),
     LastName VARCHAR(50),
     Address1 VARCHAR(50),
     Address2 VARCHAR(50)
)

INSERT INTO @OldTable (PersonID,FirstName,LastName,Address1,Address2) SELECT 1, 'A', 'B', 'C', 'D'

SELECT  *
FROM    @OldTable

DECLARE @Persons TABLE(
     PersonID INT,
     FirstName VARCHAR(50),
     LastName VARCHAR(50),
     AddressID INT
)

DECLARE @Addresses TABLE(
     AddressID INT,
     Address1 VARCHAR(50),
     Address2 VARCHAR(50)
)

DECLARE @TempTable TABLE(
     PersonID INT,
     AddressID INT IDENTITY(1,1),
     Address1 VARCHAR(50),
     Address2 VARCHAR(50)
)

INSERT INTO @TempTable (PersonID,Address1,Address2) SELECT PersonID,Address1,Address2 FROM @OldTable

INSERT INTO @Addresses (AddressID,Address1,Address2)
SELECT  AddressID,
     Address1,
     Address2
FROM    @TempTable

INSERT INTO @Persons (PersonID,FirstName,LastName,AddressID)
SELECT  ot.PersonID,
     ot.FirstName,
     ot.LastName,
     t.AddressID
FROM    @OldTable ot INNER JOIN
     @TempTable t ON ot.PersonID = t.PersonID

SELECT  *
FROM    @Persons

SELECT  *
FROM    @Addresses

Answer 4

You could also use a cursor, something like this maybe:

DECLARE @AddressID INT
DECLARE @Address VARCHAR(50)
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)

DECLARE cur CURSOR FOR 
SELECT FirstName, LastName, Address
FROM olddb.dbo.tblPersons

OPEN cur
FETCH NEXT FROM cur INTO @FirstName, @LastName, @Address

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO newdb.dbo.Address(Address)
    VALUES(@Address)

    SET @AddressID = @@IDENTITY

    INSERT INTO newdb.dbo.Persions(FirstName, LastName, AddressID)
    VALUES(@FirstName, @LastName, @AddressID)

    FETCH NEXT FROM cur INTO @FirstName, @LastName, @Address
END
CLOSE cur
DEALLOCATE cur