vb.net - Copy SQL Server Table's Data to a New Table (in Code) -

i use advice on approach copying data between tables. i'm really trying avoid rdbms-specific sql... 2 tables same in respects except name.

concept:

my application uses data table (let's call tablea). reference data changes daily, want copy yesterday's off make room toady's data.

right now, use smo make exact copy of tablea (schema), unique (using date) names table, indices, keys, etc. no problemo. if there problems updating tablea today's data, can restore yesterday's tablea (tablea_<yesterdaydate>). both tables on same database.

i can't use smo's .rename, because won't rename keys & indices...

so premise.

desired:

a non-sql statement way so. heavily invested in ef6/code first in application, name of table changes each day, can't add tables/classes in dbcontext 'just in case'.

i feel dirty using low level sql...

• sql server 2012
• vb2012
• ef6/code first

p.s. i've tried few times in past implement sql statements (linq-ish) via dbcontext, , never got work-especially sps.

i can't find cleaner way this. tried encapsulate logic single function returns boolean on success/failure. please orgive roughness of code!

newtablename oldtablename_yyymmdd. have used "sourcetablename", first hack @ it.

 public function moveallrowstonewtable(oldtablename string, newschemaname string, newtablename string, indexcolumnname string) boolean         ' oldtablename = "tablea", "tableb", ...         ' newschemaname = "dbo"         ' newtablename = "tablea_20140325", etc.         ' indexcolumnname = "id" in case, whatever col needed         '         try             '===             '             ' drop target table (if exist)             '             if me.doestableexist(newschemaname, newtablename)                 dim cmdq = me.ctx.database.executesqlcommand("drop table " & newschemaname & "." & newtablename)                 log.debug("dropped table " & newtablename)             else                 newtablename = newschemaname & "." & newtablename                 log.debug("new table name: " & newtablename)             end if             '             dim numrows int32 = 0 ' holds # of rows in sourcetable check if copy ok             select case oldtablename                 case "tablea"                     try                         '===                         '                         ' count rows in source table                         '                         numrows = (me.ctx.tablea).count                         '                         '===                         '                         ' copy rows in source table, creating new table in process                         '                         cmdq = "select * " & newtablename & " " & oldtablename                         me.ctx.database.executesqlcommand(cmdq)                         '                         ' create pk                         '                         dim pkq string = "alter table " & newtablename & " add constraint pk_" & newtablename & "_id primary key clustered (id)"                         cmdq = ctx.database.executesqlcommand(pkq.tostring)                         '                         ' create index                         '                         dim newtableindexname string = "idx_" & newtablename.replace(".", "_") ' indices can't have '.' in them                         dim idxq string = "create index " & newtableindexname & " on " & newtablename & " (" & indexcolumnname & ");"                         cmdq = ctx.database.executesqlcommand(idxq.tostring)                         '                         ' verify rows copied                         '                         dim rowq int32 = me.ctx.database.sqlquery(of int32)("select count(*) rows " & newtablename).firstordefault                         log.debug("targettablerows: " & rowq.tostring)                         if numrows = rowq                             log.debug("match!" & rowq)                         else                             log.debug("no match! numrows: " & numrows & " rowq: " & rowq)                             '                             ' rollback move-the copy failed!                             '                             ' xxx to-do                         end if                     catch ex exception                         [...]                     end try                     '                 case "tableb"                     [...] 

i have 3 source tables, switch/case practical in stage...

i'm still open ideas, dug far could, , have move on. maybe i'll re-visit more elegant comes along!