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?
- 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
Post a Comment