Transfer of Data from one table to another table

• Meera

  SSCommitted

  Points: 1966

  More actions

  May 5, 2009 at 11:01 pm

  Go to Answer

  #220831

  Dear All,

  A table is gonna get updated daily many times. like below table the fields are filled manually.

  T1-Table 1

  F1--|F2--|F3---|

  -V1-|V2--|01/04/09

  -V3-|-V4-|01/04/09

  Wen it keeps on growing the user have to scroll down till the end of the table which they feel hard.

  So we planned to move the data from one table to another (internal- not xposed) periodically like the data for the past week should alone move to another one, ie, the data should get deleted from the first one once after the data been transfered. so all the data ll be stored separately in an internal table. Have any one have done this b4? kindly help me with ur ideas...

  Thanks,

  Swadeen.

• hayzer

  Say Hey Kid

  Points: 713

  More actions

  May 6, 2009 at 1:56 am

  #989363

  BEGIN TRAN

  INSERT INTO Table2

  SELECT*

  FROMTable1

  DELETE

  FROMTable1

  COMMIT

  GO

  This code will do the trick...

  But one question mate.... Why don't you just develop Form (Application), where the user can add records?

• arun.sas

  SSChampion

  Points: 11831

  More actions

  May 6, 2009 at 2:37 am

  #989375

  Hi,

  try this

  step 01)

  /*this statement ensures that daily records backup in the internal_table*/

  insert into internal_table

  select * from Main_table

  where date = DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)

  step 02)

  /*this statement ensures only current date record in the main table*/

  delete from Main_table

  where date = DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)

  and make this as a job and schedule this in night hours

  ARUN SAS

• Meera

  SSCommitted

  Points: 1966

  More actions

  May 10, 2009 at 11:51 pm

  #991898

  Hi Can you help me providing the steps to create a job? becoz i ve nt tried tht b4.. plz..

  thanks in advance,

• myheadhurts

  Ten Centuries

  Points: 1262

  More actions

  May 11, 2009 at 3:03 pm

  Answer

  #992483

  - Open Management Studio

  - In the Object Explorer pane, expand SQL Server Agent

  - Right Click 'Jobs': choose 'New Job...'

  - On General Page: fill in Name field: "Job Whatever"

  - On Steps Page: Click 'New...' button

  - Fill in Step Name field: "Step Whatever"

  - Fill in Database field: {your db name here}

  - Fill in Command field: {the code hayzer or arun.sas posted to do the work}

  - Click 'OK'

  - On Schedules Page: Click 'New...' button

  - Fill in self explanatory fields to set up a schedule

  - Click 'OK'

  - Click 'OK'

  -MarkO

  "You do not really understand something until you can explain it to your grandmother" - Albert Einstein