how to write this query

Question

how to write this query

Mad-Dog

SSCertifiable

Points: 6117
More actions
March 6, 2011 at 3:17 am

#150043

Hi,
I've 2 server with the same tables and i need to move data between the DEV to the PROD.
my question is how i can write the tsql that will move all data from table A to table B while not the duplicate values that exists.
this is the duplicate values tsql that exists on the tables while the primary keys are timestamp and point_id
select A.timestamp,A.point_id,A._VAL from DEV.DEVTABLE.dbo.DATA_LOG A
join PROD.PROTABLE.dbo.DATA_LOG B
on A.timestamp = B.timestamp and A.point_id = B.point_id collate Latin1_General_CI_AS
and ( A.timestamp between '2011-03-02 00:00:00.00' and '2011-03-02 23:59:59.00') and A._VAL is not null
how i can write the insert from the DEV to the PROD without the DUP values?
THX

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Mad-Dog (3/6/2011)
Hi,
I've 2 server with the same tables and i need to move data between the DEV to the PROD.
my question is how i can write the tsql that will move all data from table A to table B while not the duplicate values that exists.
this is the duplicate values tsql that exists on the tables while the primary keys are timestamp and point_id
select A.timestamp,A.point_id,A._VAL from DEV.DEVTABLE.dbo.DATA_LOG A
join PROD.PROTABLE.dbo.DATA_LOG B
on A.timestamp = B.timestamp and A.point_id = B.point_id collate Latin1_General_CI_AS
and ( A.timestamp between '2011-03-02 00:00:00.00' and '2011-03-02 23:59:59.00') and A._VAL is not null
how i can write the insert from the DEV to the PROD without the DUP values?
THX

Where are the dupes? In "A", in "B", or when they join or all the above?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gofrancesc Hall of Fame Points: 3269 More actions · Answer 2

Hi,

change your JOIN into a LEFT JOIN, and select all rows that aren't in PROD;

select A.timestamp,A.point_id,A._VAL from DEV.DEVTABLE.dbo.DATA_LOG A

LEFT join PROD.PROTABLE.dbo.DATA_LOG B

on A.timestamp = B.timestamp and A.point_id = B.point_id collate Latin1_General_CI_AS

and ( A.timestamp between '2011-03-02 00:00:00.00' and '2011-03-02 23:59:59.00') and A._VAL is not null

WHERE B.timestamp IS NULL

Noy you only need an INSERT statement before your SELECT.

Regards,

Francesc

Yogesh Potdar SSC Enthusiast Points: 192 More actions · Answer 3

try this as well, not sure this is the right way .. but this might work ..

select timestamp,point_id,_VAL from DEV.DEVTABLE.dbo.DATA_LOG

where timestamp between '2011-03-02 00:00:00.00' and '2011-03-02 23:59:59.00'

except

(

select timestamp,point_id,_VAL from PROD.PROTABLE.dbo.DATA_LOG

where timestamp between '2011-03-02 00:00:00.00' and '2011-03-02 23:59:59.00'

)