sql script with local variables and

• rene.schulz@implico.com

  Default port

  Points: 1400

  More actions

  April 12, 2011 at 8:10 am

  #148805

  Dear @all, I've tried to backup a list of tables to a backup database. But how I can use the curser in the local script? I was also not able to create a procedure and could not call this with fix declared local variables.

  I'm a newbie in this and hope you could help me. I would like to replace the local variable @table_id from a fix entered value to a cursor for select * from QTO_BCKUP_BASIS.dbo.customer

  At the end the following statement should work with the cursor for all tables which are created in a table to loop over all these objects:

  exec ('select * INTO ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @table_id + ' from ' + @SAP_db_id + '.' + @SAP_schema_id + '.' + @table_id + ';')

  Fix statement without cursor:

  DECLARE @SAP_db_id varchar(3);

  DECLARE @SAP_schema_id varchar(3);

  DECLARE @BCKUP_db_id varchar(20);

  DECLARE @BCKUP_schema_id varchar(3);

  DECLARE @table_id varchar(100);

  DECLARE @BASIS_tables_id varchar(100);

  DECLARE @IsUserTable_id varchar(20);

  SET @SAP_db_id = 'QTO';

  set @SAP_schema_id = 'qto';

  SET @BCKUP_db_id = 'QTO_BCKUP_BASIS';

  set @BCKUP_schema_id = 'dbo';

  SET @table_id = 'TBD06';

  SET @IsUserTable_id = 'IsUserTable';

  SET @BASIS_tables_id = 'customer';

  exec ('use ' + @BCKUP_db_id + ';')

  exec ('IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N''' + @BCKUP_schema_id + '.' + @BASIS_tables_id + ''') AND OBJECTPROPERTY(id, N''' + @IsUserTable_id + ''') = 1) DROP TABLE ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @BASIS_tables_id + ';')

  exec ('CREATE TABLE ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @BASIS_tables_id + ' (object char(50));')

  exec ('INSERT INTO ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @BASIS_tables_id + ' (object)

  SELECT ''RFCATTRIB'' UNION ALL

  SELECT ''RFCCHECK'' UNION ALL

  SELECT ''RFCDES'' UNION ALL

  SELECT ''RFCDESSECU'' UNION ALL

  SELECT ''RFCSYSACL'' UNION ALL

  SELECT ''RFCTRUST'' UNION ALL

  SELECT ''RFCSYSACL'' UNION ALL

  SELECT ''RFCTRUST'' UNION ALL

  SELECT ''DBCON'' UNION ALL

  SELECT ''EDIPOA'' UNION ALL

  SELECT ''EDIPOD'' UNION ALL

  SELECT ''EDIPORT'' UNION ALL

  SELECT ''FILENAMECI'' UNION ALL

  SELECT ''FILEPATH'' UNION ALL

  SELECT ''FILETEXTCI'' UNION ALL

  SELECT ''PARAMVALUE'' UNION ALL

  SELECT ''PATH'' UNION ALL

  SELECT ''PATHTEXT'' UNION ALL

  SELECT ''QSENDDEST'' UNION ALL

  SELECT ''RSBASIDOC'' UNION ALL

  SELECT ''IDOCSYN'' UNION ALL

  SELECT ''RZLLITAB'' UNION ALL

  SELECT ''SSF_PSE_D'' UNION ALL

  SELECT ''SSF_PSE_H'' UNION ALL

  SELECT ''SXDOMAINS'' UNION ALL

  SELECT ''SXNODES'' UNION ALL

  SELECT ''SXROUTE'' UNION ALL

  SELECT ''SXSERV'' UNION ALL

  SELECT ''TBD00'' UNION ALL

  SELECT ''TBD00T'' UNION ALL

  SELECT ''TBD05'' UNION ALL

  SELECT ''TBD06'' UNION ALL

  SELECT ''BTCEVTJOB'' UNION ALL

  SELECT ''TBTCO'' UNION ALL

  SELECT ''TBTCP'' UNION ALL

  SELECT ''TBTCR'' UNION ALL

  SELECT ''TBTCS'' UNION ALL

  SELECT ''TPRI_PAR'' UNION ALL

  SELECT ''BTCCTL'' UNION ALL

  SELECT ''TPFBA'' UNION ALL

  SELECT ''TPFID'' UNION ALL

  SELECT ''USER_DIR'' UNION ALL

  SELECT ''VARI'' UNION ALL

  SELECT ''VARID'' UNION ALL

  SELECT ''VARINUM'' UNION ALL

  SELECT ''VARIS'' UNION ALL

  SELECT ''VARIT''

  GO')

  exec ('select * from ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @BASIS_tables_id + ';')

  exec ('IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N''' + @BCKUP_schema_id + '.' + @table_id + ''') AND OBJECTPROPERTY(id, N''' + @IsUserTable_id + ''') = 1) DROP TABLE ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @table_id + ';')

  exec ('select * INTO ' + @BCKUP_db_id + '.' + @BCKUP_schema_id + '.' + @table_id + ' from ' + @SAP_db_id + '.' + @SAP_schema_id + '.' + @table_id + ';')

• rene.schulz@implico.com

  Default port

  Points: 1400

  More actions

  April 12, 2011 at 8:14 am

  #1310410

  . please delete .