SP data to copy into another DB

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)

SP data to copy into another DB

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 04:09:45

Hi..

i have sql script which i am running on PAS_RDB database,becaz all my tables are in the PAS_RDB database,

Now i want when ever i executed the script the data should go into PAS_AUDIT database,in this database i created a table called DAILY_RECORD_COUNT manually

This is my script could u plz tell me where to makes changes for copping this into PAS_AUDIT database

use PAS_RDB
DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT
go
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES, getdate() as RUN_DATE
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1

thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:12:14

Make final insert to PAS_AUDIT.dbo.DAILY_RECORD_COUNT table using SELECT query

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 04:23:08

hi visakh,

is this script is enough? to include at the end my actual script

IF (Object_ID(N'DAILY_RECORD_COUNT')IS NOT NULL)
DROP DAILY_RECORD_COUNT
Insert into PAS_AUDIT.dbo.DAILY_RECORD_COUNT (Table_Name,Row_Count,No_of_Inserts,No_of_Updates,Run_Date)

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:41:07

quote:
Originally posted by satya068

hi visakh,

is this script is enough? to include at the end my actual script

IF (Object_ID(N'DAILY_RECORD_COUNT')IS NOT NULL)
DROP DAILY_RECORD_COUNT
Insert into PAS_AUDIT.dbo.DAILY_RECORD_COUNT (Table_Name,Row_Count,No_of_Inserts,No_of_Updates,Run_Date)

Satya

Yup.followed by your SELECT

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 04:52:37

HI..

I used the below scrrpt

INSERT INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT (TABLE_NAME,ROW_COUNT,NO_OF_INSERTS,NO_OF_UPDATES,RUN_DATE)
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES, getdate() as RUN_DATE
FROM TOTAL_RECORD_COUNT

when i opend the FULL_PAS_DAILY_RECORD_COUNT in PAS_AUDIT
there is no data in this table.

did i done ant thing wrong in the script.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 05:06:15

did you had any data in TOTAL_RECORD_COUNT?

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 05:15:40

YES...VISAKH

i got 521 records in TOTAL_RECORD_COUNT in PAS_RDB database.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-07 : 05:37:56

hi visakh..

i got the data into PAS_AUDIT databse, thanx for ur help.

could u tell how to create this as stored procedure in PAS_AUDIT database.

and i would like to run this every day morning at 8:30am

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources