February 15, 2012 at 1:26 am
Hi All, I am very new to databases so please bare with me. I have the following Stored Procedure.
CREATE PROCEDURE VRWatsysProc
AS
INSERT INTO dbo.VRWATSYSTABLE
SELECT *
FROM [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable]
ORDER BY DateTime
GO
MY problem is there are records in my source table [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable] that do not change, in this case i want to ignore these rows and not transfer them into my destination table dbo.VRWATSYSTABLE.
What is the best method to do this??
DO i first transfer the complete table and then check for duplicates and delete them or can i prevent the duplicates in my select query from being inserted into the table.
Rheinhardt
February 15, 2012 at 1:42 am
How do you define a row as not having changed?
If a row has changed, do you want to put a new version of it into VRWATSYSTABLE, or update the existing row?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2012 at 1:51 am
I have data within my rows. Timestamp Data. When i have a different timestamp in my source table i simply want to add a record to the table dbo.VRWATSYSTABLE. My source data table has 50 record which are updated hourly, some records might change others wont. If there is not an update of a record i want to ignore that record. At the moment with my querry i am getting duplicate records as i transfer the complete table....
I will add all new data to table not update.
February 15, 2012 at 2:42 am
-Please post your target table DDL ( including constraints and indexes )
did you try this ?
INSERT INTO dbo.VRWATSYSTABLE
SELECT distinct *
FROM [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable] Src
where not exists ( select 1 from dbo.VRWATSYSTABLE Trg Where Trg.keycol1=Src.keycol1 and ... )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2012 at 3:06 am
rheinhardtp (2/15/2012)
At the moment with my querry i am getting duplicate records as i transfer the complete table....
So just copy the rows where the timestamp has changed. Without seeing the DDL, I'm guessing, but probably something like
INSERT INTO dbo.VRWATSYSTABLE (<column list>)
SELECT <column list>
FROM [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable]
WHERE TimeStampColumn > @VariableHoldingTimeStampAtLastCopy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply