sql - Moving Data From One Table To Set Of Tables -


i preparing move data existing table, set of new tables. old table, inherited, growing (in columns), , new set developed try accomodate growth. data move move data entered old table new 'system' of tables.

i've come rough logic plan , code, but, i'm pretty new in-depth in sql server, appreciate input , on best course of action.

here's basic outline, not syntatically correct (due inexperience), helps illustrate steps needed. possible in outline, in single loop, or need break down smaller actions?

  1. loop through obj_out_old table, , copy data it...

a. obj_out_old.courseid , put objectives.courseid

b. obj_out_old.objective , put objectives.objective

c. loop through outcome columns - begin

--explanation of course year , course semester:

--check year in obj_out_old.courseid (the last 2 digits - e.g. "79991208", "08" means "2008"),

--check semester in obj_out_old.courseid (the 6th digit - e.g. if equals "1" "fall", if equals "2" "spring")

--to see outcomeid outcomes table.

--code outcomeyear , outcomesemester (below):

    --if (courseid year = 2002 , courseid semester = 'fall') or (courseid year > 2002 , courseid year < 2004) or (courseid year = 2004 ,   courseid semester = 'spring')     --...translated....     if ((cast(substring(cast(courseid varchar),7,2) int) = 02) , (cast(substring(cast(courseid varchar),6,1) = 1))     or      ((cast(substring(cast(courseid varchar),7,2) int) > 02) , (cast(substring(cast(courseid varchar),7,2) < 04))     or     ((cast(substring(cast(courseid varchar),7,2) int) = 04) , (cast(substring(cast(courseid varchar),6,1) = 2))            @outcomeyear='2002'     @outcomesemester='fall'      end if      --if (courseid year = 2004 , courseid semester = 'fall') or (courseid year > 2004 , courseid year < 2008) or (courseid year = 2007 , courseid semester =   'fall')     --...translated....       if ((cast(substring(cast(courseid varchar),7,2) int) = 04) , (cast(substring(cast(courseid varchar),6,1) = 1))     or      ((cast(substring(cast(courseid varchar),7,2) int) > 04) , (cast(substring(cast(courseid varchar),7,2) < 08))     or     ((cast(substring(cast(courseid varchar),7,2) int) = 07) , (cast(substring(cast(courseid varchar),6,1) = 1))           @outcomeyear='2004'     @outcomesemester='fall'      end if       --if (courseid year = 2008 , courseid semester = 'spring') or (courseid year = 2008 , courseid semester = 'fall') or (courseid year > 2008 , courseid   year < 2011) or (courseid year = 2011 , courseid       --semester = 'spring')     --...translated....     if ((cast(substring(cast(courseid varchar),7,2) int) = 08) , (cast(substring(cast(courseid varchar),6,1) = 2))     or      ((cast(substring(cast(courseid varchar),7,2) int) = 08) , (cast(substring(cast(courseid varchar),6,1) = 1))     or      ((cast(substring(cast(courseid varchar),7,2) int) > 08) , (cast(substring(cast(courseid varchar),7,2) < 11))     or     ((cast(substring(cast(courseid varchar),7,2) int) = 11) , (cast(substring(cast(courseid varchar),6,1) = 2))           @outcomeyear='2008'     @outcomesemester='spring'      end if       --if (courseid year = 2011 , courseid semester = 'fall') or (courseid year > 2011 , courseid year < 2013) or (courseid year = 2013 , courseid semester =   'spring')     --...translated....     if ((cast(substring(cast(courseid varchar),7,2) int) = 11) , (cast(substring(cast(courseid varchar),6,1) = 1))     or      ((cast(substring(cast(courseid varchar),7,2) int) > 11) , (cast(substring(cast(courseid varchar),7,2) < 13))     or     ((cast(substring(cast(courseid varchar),7,2) int) = 13) , (cast(substring(cast(courseid varchar),6,1) = 2))           @outcomeyear='2011'     @outcomesemester='fall'      end if       --if (courseid year = 2013 , courseid semester = 'fall') or (courseid year > 2013 , courseid year < 2015) or (courseid year = 2015 , courseid semester =   'spring')     --...translated....     if ((cast(substring(cast(courseid varchar),7,2) int) = 13) , (cast(substring(cast(courseid varchar),6,1) = 1))     or      ((cast(substring(cast(courseid varchar),7,2) int) > 13) , (cast(substring(cast(courseid varchar),7,2) < 15))     or     ((cast(substring(cast(courseid varchar),7,2) int) = 15) , (cast(substring(cast(courseid varchar),6,1) = 2))           @outcomeyear='2013'     @outcomesemester='fall'      end if 

--once find outcomes set, --match outcome number in column name (from obj_out_old table) outcomes.outcomenumber

--explanation of outcome number in column name (from obj_out_old table): --if column name has "_a" in title, classified outcometype "general" in outcomes table. --for example, column obj_out_old.out1_a outcomes.outcometype="general", outcomes.outcomenumber="1" --thus, "_a" suffix dropped (not carried on new tables)

get outcome number (in column name, e.g. "out1" "1" being outcome number)   @old_table_outcome_number=[outcome number column name]        ~~~~~~~     if (no '_a' in column title) , ((@outcomeyear='2002' , @outcomesemester='fall') or (@outcomeyear='2004' , @outcomesemester='fall') or   (@outcomeyear='2008' , @outcomesemester='spring') or       (@outcomeyear='2011' , @outcomesemester='fall') or (@outcomeyear='2013' , @outcomesemester='fall'))           @outcometype='specific'      else if ('_a' in column title) , (@outcomeyear='2008' , @outcomesemester='spring')           @outcometype='general'      end if     ~~~~~~~ 

--

--get outcomes.id select * outcomes  outcomes.outcomenumber=@old_table_outcome_number , outcomes.outcomeversionyear=@outcomeyear , outcomes.outcomeversionsemester=@outcomesemester , outcomes.outcometype=@outcometype 

--

--get outcome value  outcome value obj_out_old.[current outcome column] @old_table_outcome_value=obj_out_old.[current outcome column]   if (the column not have "p" 4th character in column name,  , column's value has either "n" "c" "p" or "n/a" values)   @nonpriorityvar=@old_table_outcome_value  end if  if (the column has "p" 4th character in column name , column's value has either 0 or 1)  @priorityvar='priority' 

d. put outcomes.outcomeid obj_out table

e. objectives.id (from step , b) , put obj_out (same row step d).

f. obj_out.id (from row in step d).

g. outcome value (from step 4) , match ncp table.

if @nonpriorityvar <> ''  select id ncp  ncp_descr=@nonpriorityvar  end if  --...or ncp_priority table if @priorityvar<>''   select id ncp_priority  ncp_priority_desc=@priorityvar end if 

h. put ncp.id ncp_out.ncp_id

i. if outcomes table row has priority "true," put ncp_priority.id (where ncp_priority.description="true" - step 8a.) ncp_out.

priority_id=ncp_priority.id (from step g.)

ncp_out.ncp_priority_value="true"

j. put obj_out.id (from step 6) ncp_out.objoutid

k. repeat process next row of obj_out_old table.

the tables scripted (below):

--the existing table (objout_old) -  --i've included several rows of sample data, illustrate:  --==================   set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[obj_out_old](     [id] [int] identity(1,1) not null,     [courseid] [int] not null,     [objective] [text] null,     [out1] [varchar](20) null,     [out2] [varchar](20) null,     [out3] [varchar](20) null,     [out4] [varchar](20) null,     [out5] [varchar](20) null,     [out6] [varchar](20) null,     [out7] [varchar](20) null,     [out8] [varchar](20) null,     [out9] [varchar](20) null,     [out10] [varchar](20) null,     [out11] [varchar](20) null,     [out12] [varchar](20) null,     [out13] [varchar](20) null,     [out14] [varchar](20) null,     [out15] [varchar](20) null,     [outp1] [bit] null,     [outp2] [bit] null,     [outp3] [bit] null,     [outp4] [bit] null,     [outp5] [bit] null,     [outp6] [bit] null,     [outp7] [bit] null,     [outp8] [bit] null,     [outp9] [bit] null,     [outp10] [bit] null,     [outp11] [bit] null,     [outp12] [bit] null,     [outp13] [bit] null,     [outp14] [bit] null,     [outp15] [bit] null,     [out1_a] [varchar](20) null,     [out2_a] [varchar](20) null,     [out3_a] [varchar](20) null,     [outp1_a] [bit] null,     [outp2_a] [bit] null,     [outp3_a] [bit] null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_obj_out_old] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary] textimage_on [primary] go set ansi_padding off go set identity_insert [dbo].[obj_out_old] on insert [dbo].[obj_out_old] ([id], [courseid], [objective], [out1], [out2], [out3], [out4], [out5], [out6], [out7], [out8], [out9], [out10], [out11], [out12],   [out13], [out14], [out15], [outp1], [outp2], [outp3], [outp4], [outp5], [outp6], [outp7], [outp8], [outp9], [outp10], [outp11], [outp12], [outp13], [outp14],   [outp15], [out1_a], [out2_a], [out3_a], [outp1_a], [outp2_a], [outp3_a], [lastupdate], [date_created]) values (26, 78442304, n'identifies', n'c', n'c', n'n/a',   n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', n'n/a', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, n'n/a', n'n/a',   n'n/a', 0, 0, 0, cast(0x000095fc00f61800 datetime), null) insert [dbo].[obj_out_old] ([id], [courseid], [objective], [out1], [out2], [out3], [out4], [out5], [out6], [out7], [out8], [out9], [out10], [out11], [out12],   [out13], [out14], [out15], [outp1], [outp2], [outp3], [outp4], [outp5], [outp6], [outp7], [outp8], [outp9], [outp10], [outp11], [outp12], [outp13], [outp14],   [outp15], [out1_a], [out2_a], [out3_a], [outp1_a], [outp2_a], [outp3_a], [lastupdate], [date_created]) values (117, 77132204, n'develop', n'n', n'n', n'n', n'n',   n'n', n'n/a', n'n/a', n'n', n'n/a', n'n', n'n/a', n'n', n'n/a', n'n', n'n/a', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, null, null, null, null, null,   cast(0x000095fe00ea8ee0 datetime), null) insert [dbo].[obj_out_old] ([id], [courseid], [objective], [out1], [out2], [out3], [out4], [out5], [out6], [out7], [out8], [out9], [out10], [out11], [out12],   [out13], [out14], [out15], [outp1], [outp2], [outp3], [outp4], [outp5], [outp6], [outp7], [outp8], [outp9], [outp10], [outp11], [outp12], [outp13], [outp14],   [outp15], [out1_a], [out2_a], [out3_a], [outp1_a], [outp2_a], [outp3_a], [lastupdate], [date_created]) values (123, 78422204, n'strengthen', n'n/a', n'c', n'n',   n'n', n'c', n'c', n'c', n'', n'', n'n/a', n'c', n'n/a', n'c', n'n', n'n/a', 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, null, null, null, null, null, null, cast  (0x000095fe00f58b60 datetime), null) insert [dbo].[obj_out_old] ([id], [courseid], [objective], [out1], [out2], [out3], [out4], [out5], [out6], [out7], [out8], [out9], [out10], [out11], [out12],   [out13], [out14], [out15], [outp1], [outp2], [outp3], [outp4], [outp5], [outp6], [outp7], [outp8], [outp9], [outp10], [outp11], [outp12], [outp13], [outp14],   [outp15], [out1_a], [out2_a], [out3_a], [outp1_a], [outp2_a], [outp3_a], [lastupdate], [date_created]) values (124, 78422204, n'develop', n'', n'c', n'c', n'c',   n'c', n'c', n'n/a', n'c', n'c', n'n/a', n'c', n'n/a', n'n/a', n'c', n'c', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, null, null, null, null, null, cast  (0x000095fe00f5d1b0 datetime), null) insert [dbo].[obj_out_old] ([id], [courseid], [objective], [out1], [out2], [out3], [out4], [out5], [out6], [out7], [out8], [out9], [out10], [out11], [out12],   [out13], [out14], [out15], [outp1], [outp2], [outp3], [outp4], [outp5], [outp6], [outp7], [outp8], [outp9], [outp10], [outp11], [outp12], [outp13], [outp14],   [outp15], [out1_a], [out2_a], [out3_a], [outp1_a], [outp2_a], [outp3_a], [lastupdate], [date_created]) values (127, 78242204, n'concepts', n'c', n'c', n'n/a',   n'n/a', n'c', n'n/a', n'c', n'c', n'c', n'n/a', n'n/a', n'n/a', n'n/a', n'n', n'n/a', 0, 1, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, null, null, null, null, null,   null, cast(0x000095ff008d2ca0 datetime), null) set identity_insert [dbo].[obj_out_old] off /****** object:  default [df__ams_conte__date___3ac340f7]    script date: 05/16/2014 12:03:31 ******/ alter table [dbo].[obj_out_old] add  constraint [df__ams_conte__date___3ac340f7]  default (getdate()) [date_created] go /****** object:  foreignkey [fk_obj_out_old_ams_courses]    script date: 05/16/2014 12:03:31 ******/ alter table [dbo].[obj_out_old]  nocheck add  constraint [fk_obj_out_old_ams_courses] foreign key([courseid]) references [dbo].[ams_courses] ([courseid]) on update cascade go alter table [dbo].[obj_out_old] check constraint [fk_obj_out_old_ams_courses] go    --///////////////////   --the new tables (6): --//////////////////   /****** object:  table [dbo].[objectives]    script date: 05/12/2014 10:27:23 ******/ set ansi_nulls on go  set quoted_identifier on go  set ansi_padding on go  create table [dbo].[objectives](     [id] [int] identity(1,1) not null,     [courseid] [int] not null,     [objective] [varchar](max) null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_objectives] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  set ansi_padding off go  alter table [dbo].[objectives]  nocheck add  constraint [fk_objectives_ams_courses] foreign key([courseid]) references [dbo].[ams_courses] ([courseid]) on update cascade go  alter table [dbo].[objectives] check constraint [fk_objectives_ams_courses] go  alter table [dbo].[objectives] add  constraint [df_objectives_date_created]  default (getdate()) [date_created] go     /////////////  /****** object:  table [dbo].[outcomes]    script date: 05/12/2014 10:27:19 ******/ set ansi_nulls on go  set quoted_identifier on go  set ansi_padding on go  create table [dbo].[outcomes](     [id] [int] identity(1,1) not null,     [outcomegroup] [varchar](50) null,     [outcometype] [varchar](50) null,     [outcomenumber] [int] null,     [outcomename] [varchar](500) null,     [outcomedescription] [varchar](max) null,     [outcomeversionyear] [varchar](50) null,     [outcomeversionsemester] [varchar](50) null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_outcomes] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  set ansi_padding off go  alter table [dbo].[outcomes] add  constraint [df_outcomes_date_created]  default (getdate()) [date_created] go      /////////////  /****** object:  table [dbo].[obj_out]    script date: 05/12/2014 10:27:12 ******/ set ansi_nulls on go  set quoted_identifier on go  create table [dbo].[obj_out](     [id] [int] identity(1,1) not null,     [objectiveid] [int] null,     [outcomeid] [int] null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_obj_out] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  alter table [dbo].[obj_out]  check add  constraint [fk_obj_out_objectives] foreign key([objectiveid]) references [dbo].[objectives] ([id]) on update cascade on delete cascade go  alter table [dbo].[obj_out] check constraint [fk_obj_out_objectives] go  alter table [dbo].[obj_out]  check add  constraint [fk_obj_out_outcomes] foreign key([outcomeid]) references [dbo].[outcomes] ([id]) on update cascade on delete cascade go  alter table [dbo].[obj_out] check constraint [fk_obj_out_outcomes] go  alter table [dbo].[obj_out] add  constraint [df_obj_out_date_created]  default (getdate()) [date_created] go    /////////////  /****** object:  table [dbo].[ncp_out]    script date: 05/12/2014 10:32:48 ******/ set ansi_nulls on go  set quoted_identifier on go  create table [dbo].[ncp_out](     [id] [int] identity(1,1) not null,     [objoutid] [int] not null,     [ncp_id] [int] null,     [ncp_value] [bit] null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_ncp_out] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  alter table [dbo].[ncp_out]  check add  constraint [fk_ncp_out_ncp] foreign key([ncp_id]) references [dbo].[ncp] ([id]) go  alter table [dbo].[ncp_out] check constraint [fk_ncp_out_ncp] go  alter table [dbo].[ncp_out]  check add  constraint [fk_ncp_out_obj_out] foreign key([objoutid]) references [dbo].[obj_out] ([id]) on update cascade on delete cascade go  alter table [dbo].[ncp_out] check constraint [fk_ncp_out_obj_out] go  alter table [dbo].[ncp_out] add  constraint [df_ncp_out_date_created]  default (getdate()) [date_created] go     /////////////  /****** object:  table [dbo].[ncp]    script date: 05/12/2014 10:26:58 ******/ set ansi_nulls on go  set quoted_identifier on go  set ansi_padding on go  create table [dbo].[ncp](     [id] [int] identity(1,1) not null,     [ncp_descr] [varchar](max) not null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_ncp] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  set ansi_padding off go  alter table [dbo].[ncp] add  constraint [df_ncp_date_created]  default (getdate()) [date_created] go   ///////////  /****** object:  table [dbo].[ncp_priority]    script date: 05/13/2014 17:16:34 ******/ set ansi_nulls on go  set quoted_identifier on go  set ansi_padding on go  create table [dbo].[ncp_priority](     [id] [int] identity(1,1) not null,     [ncp_priority_descr] [varchar](max) not null,     [lastupdate] [datetime] null,     [date_created] [datetime] null,  constraint [pk_ncp_priority] primary key clustered  (     [id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 90) on [primary] ) on [primary]  go  set ansi_padding off go  alter table [dbo].[ncp_priority] add  constraint [df_ncp_priority_date_created]  default (getdate()) [date_created] go 


Comments

Popular posts from this blog

how to proxy from https to http with lighttpd -

android - Automated my builds -

python - Flask migration error -