stored procedure - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-11 : 10:13:18

Hi,

i want a stored procedure to take 4 tables data every time i.e taking the backup of table data after i restore an existing database.Can anyone help me in this regard providing sample code through a stored procedure.

Regards,
Rajasekhar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:16:54

you cant take backup of table alone. do you mean just storing table data in some other staging db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-11 : 10:18:08

yes i need to hold the table data through stored procedure each and every time after restoration

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:19:38

you cant hold it inside stored proc. you can populate the data onto another table if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-11 : 10:21:18

ok then even better but this i need to do it through a stored procedure only.is it possible then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:23:16

quote:
Originally posted by rajasekhar857

ok then even better but this i need to do it through a stored procedure only.is it possible then?

yup. can you give more details on your tables and how you want data to be stored in another table. do you want to put merge all their data to same table or put them in different tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-11 : 10:24:34

into differet tables only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:27:41

then do something like


CREATE PROC yourproc name
AS

SELECT columns INTO table_temp1 FROM table1
SELECT columns INTO table_temp2 FROM table2
SELECT columns INTO table_temp3 FROM table3
SELECT columns INTO table_temp4 FROM table4
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-11 : 10:28:49

ok thank you and one more doubt on it if i want to save data which comes from application into tables.can you suggest me it through doing by stored procedure.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:21:08

hi for the above thing i need a Stored procedure for this like i need to create 4 backup tables for the tables dynamically and it shoud remove the data from main tables and insert the data into the backup tables dynamically.please give some sample SP For it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:23:19

quote:
Originally posted by rajasekhar857

hi for the above thing i need a Stored procedure for this like i need to create 4 backup tables for the tables dynamically and it shoud remove the data from main tables and insert the data into the backup tables dynamically.please give some sample SP For it.

what do you mean by insert the data into the backup tables dynamically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:26:08

i have to erase data from main tables and insert into the backup tables which table should be done dynamically using a SP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:28:59

quote:
Originally posted by rajasekhar857

i have to erase data from main tables and insert into the backup tables which table should be done dynamically using a SP

do you mean tables will be determined only at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:32:08

yes each and everytime i want tables to be without any data and paralley should go into the back up tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:34:32

quote:
Originally posted by rajasekhar857

yes each and everytime i want tables to be without any data and paralley should go into the back up tables.

so what all will be your inputs to procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:41:24

i am relatively new in working with this but i want tables data shoud be erased but data should go into backup tables after executing procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:43:04

quote:
Originally posted by rajasekhar857

i am relatively new in working with this but i want tables data shoud be erased but data should go into backup tables after executing procedure

can you give answer to my previous question?

what all will be your input values to procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:48:06

no input values to the procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:49:09

quote:
Originally posted by rajasekhar857

no input values to the procedure

what??
then how will you determine what all tables are to be backed up?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-12 : 01:50:40

iam having tables list and i will create a copy for those tables and now i require further steps in doing with Sp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:57:23

quote:
Originally posted by rajasekhar857

iam having tables list and i will create a copy for those tables and now i require further steps in doing with Sp

Please try to understand what I'm telling you
You've list of table and you told you need to backup 4 tables out of them which you determine runtime. so my question is shouldnt names be passed as input to sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources