April 30, 2015 at 6:11 am
Hi-
Would be glad if someone could help me configure a new stored proc that will:
assumption: table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Thanks!
April 30, 2015 at 6:49 am
emcee0808 (4/30/2015)
Hi-Would be glad if someone could help me configure a new stored proc that will:
assumption: table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Thanks!
Hi and welcome to the forums.
This is phrased like it's homework and there's not a lot of specifics. Please see the link in my signature for the type of information we need to help you out. Be sure to include what you've tried so far.
May 4, 2015 at 12:25 am
Ed Wagner (4/30/2015)
emcee0808 (4/30/2015)
Hi-Would be glad if someone could help me configure a new stored proc that will:
assumption: table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Thanks!
Hi and welcome to the forums.
This is phrased like it's homework and there's not a lot of specifics. Please see the link in my signature for the type of information we need to help you out. Be sure to include what you've tried so far.
Hi Ed-
Apologies for posting inappropriately. Anyway i have a chunk of the code and just missing the last part where I have to compare data from two tables and update when column values are not equal as well as the updatedt column. Here is how the code goes:
CREATE PROCEDURE dbo.SAMPLESP
AS
/*INSERT VERSION HISTORY FOR TRACKING*/
SET NOCOUNT ON;
/*BODY*/
-- GET VIEW DATA
SELECT * INTO dbo.#TMPTBL FROM [dbo].[VW_SAMPLE] -- contains already the current data of the view
-- TRUNCATE / DELETE [tblInactive]
DELETE FROM [dbo].[tblInactive]
-- POPULATE [tblInactive] FOR INACTIVE ONLY
INSERT INTO [dbo].[tblInactive]
(
UserID
,EnterpriseId
,FirstName
,LastName
,Unit
,[Status]
)
SELECT
UserID
,EnterpriseId
,FirstName
,LastName
,Unit
,[Status]
FROM dbo.#TMPTBL
WHERE UserID NOT IN (SELECT UserID FROM [dbo].[tblHistory] WHERE [Status] IN ('INACTIVE') )
AND [Status] = 'INACTIVE'
-- INSERT [tblHistory] DATA THAT DO NOT EXIST
INSERT INTO [dbo].[tblHistory]
(
UserID
,EnterpriseId
,FirstName
,LastName
,MiddleName
,[Status]
,InsertDt
,UpdateDt
)
SELECT
UserID
,EnterpriseId
,FirstName
,LastName
,MiddleName
,[Status]
,GETDATE()
,GETDATE()
FROM dbo.#TMPTBL
WHERE UserID NOT IN (SELECT UserID FROM [dbo].[tblHistory])
-- UPDATE [tblHistory] DATA THAT IS MISMATCHED
*missing code*
/*ERROR HANDLING*/
--ADD
DROP TABLE #TMPTBL
GO
PRINT ''
PRINT '********** END: dbo.SAMPLESP **********'
PRINT ''
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply