November 14, 2016 at 8:23 pm
Dear Friends,
I have Syntax like this :
INSERT INTO DATABASE_A.DBO.TABLE_A ----THIS IS THE LOCAL SERVER
SELECT S_C,
S_N
FROM OPENQUERY ([LINK_SERVER],'select
S_C,
S_N,
from DATABASE_B.dbo.TABLE_B')
this Syntax works fine,,
Now I want to make the Store Procedure from this syntax with truncate first the TABLE_A and then insert the syntax and then Check the integrity data..
Can someone please help me? what should I consider for declare ?
thank you very much
November 16, 2016 at 2:10 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply