Insert to linked server with out primary key

[oakgrove]

The following query runs on the SQL server box that has the 'sourcetable'. Its function is to add new records to the 'targettable' on the remote linked server. It correctly adds rows when I have the 'targettable' set with column 'phoneleadid' as the primary key. It runs but, does not add rows if I set another column as primary key and allow nulls in 'phoneleadid'. Any way to get this to run without having 'phoneleadid' be the primary key for 'targettable'? (There is no primary key on 'sourcetable'.)

insert into [67.xx.xxx.xxx].[server1].[dbo].[targettable] 
	(phoneleadid,pnumber)
	select callid,localuserid 
	from [sourcetable]
	where callid not in(select phoneleadid 
	from [67.xx.xxx.xxx].[server1].[dbo].[targettable])

Oakgrove Computer Grouper
Lansing, MI

 

[SQLDenis]

becuase you already have 1 null in you table and thus any other row with a value of null will not be inserted


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[oakgrove]

good thought however sourcetable.callid is never null

Oakgrove Computer Grouper
Lansing, MI

 

[SQLDenis]

try this for fun
select callid,localuserid
from [sourcetable]
where callid not in(select phoneleadid
from [67.xx.xxx.xxx].[server1].[dbo].[targettable]
where phoneleadid is not null)


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[oakgrove]

humm, I must be missing your point

select callid,localuserid 
    from [sourcetable]
    where callid not in(select phoneleadid 
    from [67.xx.xxx.xxx].[server1].[dbo].[targettable]
     where phoneleadid  is not null)

returns ALL the records in the source table not just the ones that haven't been transfered over yet. ???

Oakgrove Computer Grouper
Lansing, MI

 

[oakgrove]

Ok Now I got it. Thanks

Oakgrove Computer Grouper
Lansing, MI