Dear all,
I made it like this :
--CREATE PROCEDURE SP_TRUNC_INS_M_SUPP
--AS
declare @pre_var INT
DECLARE @SSQL SQL_VARIANT
DECLARE @SQL SQL_VARIANT
---TRUNCATE THE TABLE ( empty the data )
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE [NAME] = '%objectname' AND TYPE ='U')
TRUNCATE TABLE <OBJECT_NAME>
--CHECK DATA
SELECT @SSQL = COUNT(*) FROM <OBJECT_NAME>
SELECT @SQL = COUNT(*) FROM [LINKSERVER].DB_NAME.dbo.OBJECT_NAME
PRINT '*************** CHECKING DATA ***************'
PRINT '' PRINT CAST(@SSQL AS NVARCHAR) + ' ' + ' NO DATA '
IF @SSQL <> @SQL
BEGIN
PRINT ''
PRINT '*************** NO DATA ***************'
PRINT ''
PRINT '*************** NOW INSERTING DATA, PLEASE WAIT ***************'
PRINT ''
END
ELSE
BEGIN
PRINT '*************** NOT PROCESSES, DATA ROWS ALREADY SAME ***************'
END
BEGIN
SET NOCOUNT ON
Select @pre_var = count(*) From <OBJECT_NAME>
IF @pre_var = 0
BEGIN
INSERT INTO <OBJECT_NAME>
SELECT S_C, S_N
FROM OPENQUERY ([LINKSERVER],'SELECT
S_C,S_N
from <DB_NAME>.dbo.<OBJECT_NAME>')
PRINT @@ROWCOUNT
PRINT ''
PRINT ' *************** DATA SUCCESSFULLY INSERTED ***************'
END
ELSE
BEGIN
'*************** DATA ALREADY EXIST, NO INSERT AVAILABLE, PLEASE CHECK ***************'
END
END