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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site