Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exporting from old table to new tables

Author  Topic 

Starlight
Starting Member

14 Posts

Posted - 2014-09-17 : 14:58:49
Hey everyone, I'm a bit new to SQL. I've done some things with SQL scripting, but am no expert by any means.

I've been tasked with the following: Export data from old table, to newly created table(s). I say tables because there's three destination tables total, and only two source tables. The two old tables are virtually the same, so the new table will be a combination of the two. More importantly I'll need to chop out some data from the old tables and create new details with them. As you can see, the old table was very "flat" in the sense that they kept adding new fields for bits of detail data (10 for one type of detail, 15 for another type of detail). I'd like to house that data in separate linkable tables. This is an example of the old and new data structure:

OldTable1 (SOURCE)
Name | Acct | Detail1Min | Detail1Max | ~~~ | Detail10Min | Detail10Max | DiffDetail1Min | DiffDetail1Max | DiffDetail1Rate | ~~~ | DiffDetail15Min | DiffDetail15Max | DiffDetail15Rate |

OldTable2 (SOURCE)
Name | Acct | Detail1Min | Detail1Max | ~~~ | Detail10Min | Detail10Max | DiffDetail1Min | DiffDetail1Max | DiffDetail1Rate | ~~~ | DiffDetail15Min | DiffDetail15Max | DiffDetail15Rate |


| | |
| | |
VVV


NewTable (Destination)
AutoID | Name | Acct | TYPE | * Newly created field so I know which table it came from (OldTable1 or OldTable2?)

NewTableDetail (Destination)
AutoID | NewTableID | DetailMin | DetailMax |

NewTableDiffDetail (Destination)
AutoID | NewTableID | DiffDetailMin | DiffDetailMax | DiffDetailMax |

In the new data structure, the AutoID from the main table (NewTable) will link to the two newly created detail tables using NewTableID. Can someone please help? Much appreciated :)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-17 : 16:33:37
Look up INSERT-SELECT in BOL. There are examples there specific to this under a sub-heading of "Inserting data from other tables"



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-17 : 18:11:18
Forgive me, but what is BOL?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-17 : 21:31:57
Sorry for the length of this post!

This will work only if the Name/Acct combination doesn't occur across OldTable*
Otherwise the TYPE (new field to let you know where the data comes from) will contain tablename where Name/Acct first occured.

insert into NewTable ([Name],Acct,[TYPE])
select [Name]
,Acct
,'From OldTable1'
from OldTable1
group by [Name]
,Acct
;
insert into NewTable ([Name],Acct,[TYPE])
select [Name]
,Acct
,'From OldTable2'
from OldTable2
where not exists (select *
from NewTable
where NewTable.[Name]=OldTable2.[Name]
and NewTable.Acct=OldTable2.Acct
)
group by [Name]
,Acct
;

insert into NewTableDetail (NewTableID,DetailMin,DetailMax)
select b.AutoID
,a.DetailMin
,a.DetailMax
from (select [Name]
,Acct
,Detail1Min as DetailMin
,Detail1Max as DetailMax
from OldTable1
where Detail1Min is not null
or Detail1Max is not null
union all
select [Name]
,Acct
,Detail2Min as DetailMin
,Detail2Max as DetailMax
from OldTable1
where Detail2Min is not null
or Detail2Max is not null
union all
select [Name]
,Acct
,Detail3Min as DetailMin
,Detail3Max as DetailMax
from OldTable1
where Detail3Min is not null
or Detail3Max is not null
union all
select [Name]
,Acct
,Detail4Min as DetailMin
,Detail4Max as DetailMax
from OldTable1
where Detail4Min is not null
or Detail4Max is not null
union all
select [Name]
,Acct
,Detail5Min as DetailMin
,Detail5Max as DetailMax
from OldTable1
where Detail5Min is not null
or Detail5Max is not null
union all
select [Name]
,Acct
,Detail6Min as DetailMin
,Detail6Max as DetailMax
from OldTable1
where Detail6Min is not null
or Detail6Max is not null
union all
select [Name]
,Acct
,Detail7Min as DetailMin
,Detail7Max as DetailMax
from OldTable1
where Detail7Min is not null
or Detail7Max is not null
union all
select [Name]
,Acct
,Detail8Min as DetailMin
,Detail8Max as DetailMax
from OldTable1
where Detail8Min is not null
or Detail8Max is not null
union all
select [Name]
,Acct
,Detail9Min as DetailMin
,Detail9Max as DetailMax
from OldTable1
where Detail9Min is not null
or Detail9Max is not null
union all
select [Name]
,Acct
,Detail10Min as DetailMin
,Detail10Max as DetailMax
from OldTable1
where Detail10Min is not null
or Detail10Max is not null
union all
select [Name]
,Acct
,Detail11Min as DetailMin
,Detail11Max as DetailMax
from OldTable1
where Detail11Min is not null
or Detail11Max is not null
union all
select [Name]
,Acct
,Detail12Min as DetailMin
,Detail12Max as DetailMax
from OldTable1
where Detail12Min is not null
or Detail12Max is not null
union all
select [Name]
,Acct
,Detail13Min as DetailMin
,Detail13Max as DetailMax
from OldTable1
where Detail13Min is not null
or Detail13Max is not null
union all
select [Name]
,Acct
,Detail14Min as DetailMin
,Detail14Max as DetailMax
from OldTable1
where Detail14Min is not null
or Detail14Max is not null
union all
select [Name]
,Acct
,Detail15Min as DetailMin
,Detail15Max as DetailMax
from OldTable1
where Detail15Min is not null
or Detail15Max is not null
) as a
inner join NewTable as b
on b.[Name]=a.[Name]
and b.Acct=a.acct
;

insert into NewTableDiffDetail (NewTableID,DiffDetailMin,DiffDetailMax,DiffDetailRate)
select b.AutoID
,a.DiffDetailMin
,a.DiffDetailMax
,a.DiffDetailRate
from (select [Name]
,Acct
,DiffDetail1Min as DiffDetailMin
,DiffDetail1Max as DiffDetailMax
,DiffDetail1Rate as DiffDetailRate
from OldTable1
where DiffDetail1Min is not null
or DiffDetail1Max is not null
or DiffDetail1Rate is not null
union all
select [Name]
,Acct
,DiffDetail2Min as DiffDetailMin
,DiffDetail2Max as DiffDetailMax
,DiffDetail2Rate as DiffDetailRate
from OldTable1
where DiffDetail2Min is not null
or DiffDetail2Max is not null
or DiffDetail2Rate is not null
union all
select [Name]
,Acct
,DiffDetail3Min as DiffDetailMin
,DiffDetail3Max as DiffDetailMax
,DiffDetail3Rate as DiffDetailRate
from OldTable1
where DiffDetail3Min is not null
or DiffDetail3Max is not null
or DiffDetail3Rate is not null
union all
select [Name]
,Acct
,DiffDetail4Min as DiffDetailMin
,DiffDetail4Max as DiffDetailMax
,DiffDetail4Rate as DiffDetailRate
from OldTable1
where DiffDetail4Min is not null
or DiffDetail4Max is not null
or DiffDetail4Rate is not null
union all
select [Name]
,Acct
,DiffDetail5Min as DiffDetailMin
,DiffDetail5Max as DiffDetailMax
,DiffDetail5Rate as DiffDetailRate
from OldTable1
where DiffDetail5Min is not null
or DiffDetail5Max is not null
or DiffDetail5Rate is not null
union all
select [Name]
,Acct
,DiffDetail6Min as DiffDetailMin
,DiffDetail6Max as DiffDetailMax
,DiffDetail6Rate as DiffDetailRate
from OldTable1
where DiffDetail6Min is not null
or DiffDetail6Max is not null
or DiffDetail6Rate is not null
union all
select [Name]
,Acct
,DiffDetail7Min as DiffDetailMin
,DiffDetail7Max as DiffDetailMax
,DiffDetail7Rate as DiffDetailRate
from OldTable1
where DiffDetail7Min is not null
or DiffDetail7Max is not null
or DiffDetail7Rate is not null
union all
select [Name]
,Acct
,DiffDetail8Min as DiffDetailMin
,DiffDetail8Max as DiffDetailMax
,DiffDetail8Rate as DiffDetailRate
from OldTable1
where DiffDetail8Min is not null
or DiffDetail8Max is not null
or DiffDetail8Rate is not null
union all
select [Name]
,Acct
,DiffDetail9Min as DiffDetailMin
,DiffDetail9Max as DiffDetailMax
,DiffDetail9Rate as DiffDetailRate
from OldTable1
where DiffDetail9Min is not null
or DiffDetail9Max is not null
or DiffDetail9Rate is not null
union all
select [Name]
,Acct
,DiffDetail10Min as DiffDetailMin
,DiffDetail10Max as DiffDetailMax
,DiffDetail10Rate as DiffDetailRate
from OldTable1
where DiffDetail10Min is not null
or DiffDetail10Max is not null
or DiffDetail10Rate is not null
union all
select [Name]
,Acct
,DiffDetail11Min as DiffDetailMin
,DiffDetail11Max as DiffDetailMax
,DiffDetail11Rate as DiffDetailRate
from OldTable1
where DiffDetail11Min is not null
or DiffDetail11Max is not null
or DiffDetail11Rate is not null
union all
select [Name]
,Acct
,DiffDetail12Min as DiffDetailMin
,DiffDetail12Max as DiffDetailMax
,DiffDetail12Rate as DiffDetailRate
from OldTable1
where DiffDetail12Min is not null
or DiffDetail12Max is not null
or DiffDetail12Rate is not null
union all
select [Name]
,Acct
,DiffDetail13Min as DiffDetailMin
,DiffDetail13Max as DiffDetailMax
,DiffDetail13Rate as DiffDetailRate
from OldTable1
where DiffDetail13Min is not null
or DiffDetail13Max is not null
or DiffDetail13Rate is not null
union all
select [Name]
,Acct
,DiffDetail14Min as DiffDetailMin
,DiffDetail14Max as DiffDetailMax
,DiffDetail14Rate as DiffDetailRate
from OldTable1
where DiffDetail14Min is not null
or DiffDetail14Max is not null
or DiffDetail14Rate is not null
union all
select [Name]
,Acct
,DiffDetail15Min as DiffDetailMin
,DiffDetail15Max as DiffDetailMax
,DiffDetail15Rate as DiffDetailRate
from OldTable1
where DiffDetail15Min is not null
or DiffDetail15Max is not null
or DiffDetail15Rate is not null
) as a
inner join NewTable as b
on b.[Name]=a.[Name]
and b.Acct=a.acct
;

Now - do the last sql for OldTable2 aswell and you should be set.
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-18 : 09:27:49
bitsmed, thank you so much for the response. One thing though. The Acct and Name are not unique, meaning there can be many occurences of this field in OldTable1 and OldTable2. In NewTable there is an AutoID field which needs to be populated as well (starting at 1, incrementing by 1). This newly created field will then need to link to NewTableDetail and NewTableDiffDetail. The Acct is NOT unique, neither is Name, and neither is the combination of the two. How can I make it so the two new detail tables are linked via ID to the master table?

Basically the NewTable will look like this:

NewTable

AutoID | Acct | Name |
1______1234___Bob * NOTE: 1234 Bob can occur many times
2______1234___Cindy
3______4321___Bob
4______1234___Bob * NOTE: 1234 Bob can occur many times
5______6789___John

...and the DetailTable will look like this:

NewDetailTable

AutoID | NewTableID | DetailMin | DetailMax |
1___________1________100______200*Linked to NewTable.AutoID 1
2___________2________200______300*Linked to NewTable.AutoID 2
3___________3________50_______75_*Linked to NewTable.AutoID 3
4___________4________89_______90_*Linked to NewTable.AutoID 4

..and of course NewTableDiffDetail will be very similiar to the table above.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-18 : 12:12:48
BOL == Books Online (What you see when you bring up SQL Help)



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-18 : 14:32:00
This of course changes the way I read your original question.
I would start of by creating the NewTable with all columns, then fill in data from OldTables (this would give us the needed auto incremented key), then fill the NewDetail and NewDetailDiff tables, and finaly delete the unneeded columns from NewTable.
Something like:
create table NewTable (
AutoID int unsigned identify not null primary key
,[Name] varchar(255) null
,Acct int unsigned null
,DetailMin int unsigned null
,DetailMax int unsigned null
,DiffDetailMin int unsigned null
,DiffDetailMax int unsigned null
,DiffDetailRate int unsigned null
);

insert into NewTable([Name],Acct,DetailMin,DetailMax,DiffDetailMin,DiffDetailMax,DiffDetailRate)
select [Name]
,Acct
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable1
union all
select [Name]
,Acct
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable1
union all
select [Name]
,Acct
,Detail3Min
,Detail3Max
,DiffDetail3Min
,DiffDetail3Max
,DiffDetail3Rate
from OldTable1
.
. fill in the the rest yourself
.

union all
select [Name]
,Acct
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable1
union all
select [Name]
,Acct
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable2
union all
select [Name]
,Acct
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable2
.
. fill in the the rest yourself
.

union all
select [Name]
,Acct
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable2
;

insert into NewDetailTable (NewTableID,DetailMin,DetailMax)
select AutoId
,DetailMin
,DetailMax
from NewTable
;

insert into NewDetailDiffTable (NewTableID,DiffDetailMin,DiffDetailMax,DiffDetailRate)
select AutoId
,DiffDetailMin
,DiffDetailMax
,DiffDetailRate
from NewTable
;

alter table NewTable (
drop column DetailMin
,DetailMax
,DiffDetailMin
,DiffDetailMax
,DiffDetailRate
);
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-18 : 18:06:48
Thanks bitsmed, but it appears that I have to populate the incremented ID values as I'm populating the table(s) with data...for each record insertion, populate the ID field with an incremented value (He said that he cant set it to auto increment...dont know if he's just being a meanie or not
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-18 : 18:21:45
quote:
Originally posted by Starlight

Thanks bitsmed, but it appears that I have to populate the incremented ID values as I'm populating the table(s) with data...for each record insertion, populate the ID field with an incremented value (He said that he cant set it to auto increment...dont know if he's just being a meanie or not


Are you allowed to alter table settings (adding the DetailMin ... and later removing them)?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-18 : 18:48:06
Wrap it in with a row count.
Also, forgot the origin table (TYPE).
create table NewTable (
AutoID int unsigned not null primary key
,[Name] varchar(255) null
,Acct int unsigned null
,[TYPE] varchar(14) null
,DetailMin int unsigned null
,DetailMax int unsigned null
,DiffDetailMin int unsigned null
,DiffDetailMax int unsigned null
,DiffDetailRate int unsigned null
);

insert into NewTable(AutoID,[Name],Acct,[TYPE],DetailMin,DetailMax,DiffDetailMin,DiffDetailMax,DiffDetailRate)
select row_count() over (order by a.[TYPE],a.[Name],a.Acct) as AutoID
,a.*
from (select [Name]
,Acct
,'From OldTable1'
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable1
union all
select [Name]
,Acct
,'From OldTable1'
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable1
union all
select [Name]
,Acct
,'From OldTable1'
,Detail3Min
,Detail3Max
,DiffDetail3Min
,DiffDetail3Max
,DiffDetail3Rate
from OldTable1
.
. fill in the the rest yourself
.
union all
select [Name]
,Acct
,'From OldTable1'
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable1
union all
select [Name]
,Acct
,'From OldTable2'
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable2
union all
select [Name]
,Acct
,'From OldTable2'
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable2
.
. fill in the the rest yourself
.
union all
select [Name]
,Acct
,'From OldTable2'
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable2
) as a
;

insert into NewDetailTable (NewTableID,DetailMin,DetailMax)
select AutoId
,DetailMin
,DetailMax
from NewTable
;

insert into NewDetailDiffTable (NewTableID,DiffDetailMin,DiffDetailMax,DiffDetailRate)
select AutoId
,DiffDetailMin
,DiffDetailMax
,DiffDetailRate
from NewTable
;

alter table NewTable (
drop column DetailMin
,DetailMax
,DiffDetailMin
,DiffDetailMax
,DiffDetailRate
);
Go to Top of Page

Upendra Gupta
Starting Member

12 Posts

Posted - 2014-09-19 : 06:24:18
unspammed
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-19 : 09:58:29
quote:
Originally posted by bitsmed

quote:
Originally posted by Starlight

Thanks bitsmed, but it appears that I have to populate the incremented ID values as I'm populating the table(s) with data...for each record insertion, populate the ID field with an incremented value (He said that he cant set it to auto increment...dont know if he's just being a meanie or not


Are you allowed to alter table settings (adding the DetailMin ... and later removing them)?



No, unfortunately not. The table structure is already created (look at my first post for example of how it looks)
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-19 : 13:53:51
Are there any fields (or combination of field) that make a record unique in OldTable1?
Does this apply for OldTable2?
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-19 : 14:20:30
Yes..

OldTable1: Acct, City, State, Flag (those 4 make it unique)
OldTable2: Acct (Just this one makes it unique)

I'm reading your last example, and just wanted to note that the detail fields do not exist in NewTable...they only exist in NewDetailTable and NewDetailDiffTable.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-19 : 15:17:55
This is very much open for optimization, as it will read your oldtable's 3 times:

declare @newid int;
declare @newdetailid int;
declare @newdetaildiffid int;

select @newid=max(a.AutoID)
,@newdetailid=max(b.AutoId)
,@newdetaildiffid=max(c.AutoID)
from NewTable as a
left outer join NewDetailTable as b
on b.NewTableId=a.AutoId
left outer join NewDetailDiffTable as c
on c.NewTableId=a.AutoID
;

insert into NewTable (AutoID,Acct,[Name])
select AutoId+@newid
,Acct
,[Name]
from (select row_count() over (order by a.[TYPE],a.Acct,a.City,a.State,a.Flag) as AutoID
,a.*
from (select [Name]
,Acct
,City
,State
,Flag
,'From OldTable1' as [TYPE]
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable1
union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable1' as [TYPE]
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable1
union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable1' as [TYPE]
,Detail3Min
,Detail3Max
,DiffDetail3Min
,DiffDetail3Max
,DiffDetail3Rate
from OldTable1
.
. fill in the the rest yourself
.

union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable1' as [TYPE]
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable1
union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable2' as [TYPE]
,Detail1Min
,Detail1Max
,DiffDetail1Min
,DiffDetail1Max
,DiffDetail1Rate
from OldTable2
union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable2' as [TYPE]
,Detail2Min
,Detail2Max
,DiffDetail2Min
,DiffDetail2Max
,DiffDetail2Rate
from OldTable2
[/red].
. fill in the the rest yourself
.[/red]
union all
select [Name]
,Acct
,City
,State
,Flag
,'From OldTable2' as [TYPE]
,Detail15Min
,Detail15Max
,DiffDetail15Min
,DiffDetail15Max
,DiffDetail15Rate
from OldTable2
) as a
) as a
;

insert into NewDetailTable (AutoID,NewTableID,DetailMin,DetailMax)
select AutoId+@newdetailid
,AutoId+@newid
,DetailMin
,DetailMax
from (***** insert the green portion from above *****
) as a
;

insert into NewDetailDiffTable (AutoID,NewTableID,DetailDiffMin,DetailDiffMax,DetailDiffRate)
select AutoId+@newdetaildiffid
,AutoId+@newid
,DetailDiffMin
,DetailDiffMax
,DetailDiffRate
from (***** insert the green portion from above *****
) as a
;
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-22 : 08:13:45
Unfortunately C# isn't allowing me to declare sql variables through code. Is anyone familiar with a way around this?
Go to Top of Page

Starlight
Starting Member

14 Posts

Posted - 2014-09-23 : 14:30:11
OK, I think Im taking a different approach to this...if bitsmed or anyone else can point me in the right direction, I'd be so appreciative This should actually be easier.

I have both old tables, combined into one Newtable (including the ID field, Type field, etc) already populated.

So now it looks like this, all populated (ID is key field):

CurrentMainTable (SOURCE)
ID | Type | Name | Acct | Detail1Min | Detail1Max | ~~~ | Detail10Min | Detail10Max | DiffDetail1Min | DiffDetail1Max | DiffDetail1Rate | ~~~ | DiffDetail15Min | DiffDetail15Max | DiffDetail15Rate |

I need to be able to transfer the data from the detail fields to two new tables (which are already created) and they must link back to the ID field of CurrentMainTable


| | |
| | |
| | |
VVV

NewTableDetail (Destination)
ID | CurrentMainTableID (ID field from main table) | DetailMin | DetailMax |

NewTableDiffDetail (Destination)
ID | CurrentMainTableID (ID field from main table) | DiffDetailMin | DiffDetailMax | DiffDetailMax |

Can someone please help?
Go to Top of Page
   

- Advertisement -