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

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

-------------Problems Reply------------

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.

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

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

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

Category:tsql Views:0 Time:2009-12-02

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.138 (s). 11 q(s)