Split temp table into two tables.

Hi,

I have some data that needs to be imported as a one-time job and I don't know how to create the link between them.  

OrigTable:
-------------

Control
Suffix
Account
RRName
Address1
Address2

Needs to be split into two tables:

PolicyHolder:
-----------------

UID (uniqueidentifier)
Control
Suffix
Account
AddressID (uniqueidentifier)

Address:
-----------

AddressiD (UniqueIdentifier (linked to PolicyHolder.AddressID))
RRName
Address1
Address2
LVL 2
prositAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
can you post an example of the source data and the *exact* output you want in both tables?

Note that you even might want to add another table, because one AddresID can have multiple Addresses (think about the scenario where a AddresID has 3 Adresses)

JeffCoachman
0
prositAuthor Commented:
Actually for the addresses it's just one address, they messed up when they created the table.  It's a one-to-one.

OrigTable:
-------------

Control -> PolicyHolderTable
Suffix -> PolicyHolderTable
Account -> PolicyHolderTable
RRName  -> AddressTable
Address1 -> AddressTable
Address2 -> AddressTable

When inserting into the PolicyHolderTable a uniqueid should be created that links to the about to be insert row in the AddressTable.

Does that make sense?

~j
0
Aneesh RetnakaranDatabase AdministratorCommented:
You can create a temp table with the exact structure as source,
 the alter the temp table to add uniqueidentifier with default value  NEWID()
alter table temp add ud uniqueidentifier not null default newid()
later you can populate the data into the other tables
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PadawanDBAOperational DBACommented:
Not sure if this is what you're looking for, but you can try this and see if it yields what you're looking for (or gives you enough to go on).  I didn't really see any specification for your requirements on the primary keys, so I just used an identity column as a surrogate.  It's always a good idea to figure out what you need from your primary key and/or what the data lends itself to needing from its primary key.  I would have loved to give you temp table definitions for this, but foreign key constraints aren't supported in temp tables.  If possible, copy the data you are looking to mess with into a new database and you can test this out there:
create table AddressTable
(
	addressID int identity( 1, 1 ),
	RRName nvarchar(20) not null,
	Address1 nvarchar( 20 ) not null,
	Address2 nvarchar( 20 ) null,
	constraint PK_AddressTable_AddressID primary key clustered( addressID )
);



create table PolicyHolder
(
	PolicyHolderID int identity( 1, 1 ),
	"control" nvarchar( 10 ) not null,
	suffix nvarchar( 5 ) null,
	account nvarchar( 15 ) not null,
	addressID int not null references AddressTable( addressID ),
	constraint PK_PolicyHolder_PolicyHolderID primary key clustered( PolicyHolderID ),
	constraint FK_AddressID_ref_AddressTable foreign key( addressID ) references AddressTable( addressID )
);

insert into AddressTable
	select
		RRName,
		Address1,
		Address2
	from
		OrigTable;

insert into PolicyHolder
	select
		OT.control,
		OT.suffix,
		OT.account,
		AT.addressID
	from
		OrigTable as OT
			inner join AddressTable as AT on OT.RRName = AT.RRname and OT.Address1 = AT.Address1 and OT.Address2 = OT.Address2;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
That code assumes that RRName and addresses are unique among all policy holders -- that may be true or it may not, but personally I wouldn't write code that assumes that it is.
0
PadawanDBAOperational DBACommented:
Very true, Scott.  I neglected to think about that - and also should have accounted for it.  I very rarely get to work with the architecture/design side of database administration.  Perhaps this might account for that in lieu of the previous insert:

if object_id( 'tempdb..#AddressStaging' ) is not null
	drop table #AddressStaging;

select distinct
	RRName,
	Address1,
	Address2
into
	#AddressStaging
from
	OrigTable;

insert into AddressTable
	select
		RRName,
		Address1,
		Address2
	from
		#AddressStaging;

Open in new window


I am also assuming that there are no null values in the data (aside from Address2 and suffix because I wasn't exactly sure of the data contents)
0
prositAuthor Commented:
I was able to resolve it by a mix of comments using temp tables.  

Thank you for  helping out!

~j
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.