Insert / Select and Variable help 1

[Honyo]

Afternoon All,

I deperately need some help here.

I have SQL installed on 2 different servers DSQL and VSQL.
I have a database called AVS with a table called Transaction_Info (on both servers).
DSQL and VSQL are linked server via VSQL.

I need to run a query that will check a column on VSQL (local server)called Transacton_ID and pull the highest value. It will pass this value to a variable.

The next portion will then connect to the other SQL server DSQL, take the variable and compare it to that column, it there are any values higher than the variable, it will insert all the values higher than the variable into the local (VSQL) table.

I have this:

USE AVS

DECLARE @transid varchar(30)
select @transid = (select MAX(Transaction_ID) from Transaction_Info)

INSERT INTO Transaction_Info SELECT * FROM DSQL.AVS.dbo.Transaction_Info
where Transaction_ID > @transid

This works well. Now the problem (-:
All values start with 1 or 2. I need the variable to only search through the values that start with 1 and then follow the above route.

I have:

USE AVS

DECLARE @transid varchar(30)
select @transid = (select MAX(Transaction_ID) from Transaction_Info
where left(Transaction_ID,1) = 1)

INSERT INTO Transaction_Info SELECT * FROM DSQL.AVS.dbo.Transaction_Info
where Transaction_ID > @transid

This does not work )-: and I have no idea why. Any suggestions?

Regards
Honyo

 

[mrdenny]

when you are searching the table on the local server you are searching for the max record that starts with a 1, but when you search from the DSQL server you are selecting all records. Don't you need to add " and left(transaction_id, 1) = 1" to the Insert into command to get the result you want?

I might be mis-understanding you.

Are you getting an error message of some sort?

Denny

 

[Honyo]

Hi

When run without the "where left(Transaction_ID,1) = 1)" it works. It will check the local table, find the highest value, dump it into the @transid variable. It then goes to the remote SQL server and then uses the vaible to do a selct of all records that are higher in value. It will then insert all these records into the local table.

The remote server only has records that start with 1. The local table however has 1's and 2's in it.

If I include the "where left(Transaction_ID,1) = 1)" I get the following message:

Server: Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK_Transaction_Info'. Cannot insert duplicate key in object 'Transaction_Info'.
The statement has been terminated.

Regards
Guy

 

[mrdenny]

Hi,
Sounds like you've got a record in the DSQL server that is greater than the highest record in VSQL, but does not start with a one.

That's the only expeination for a Primary Key violation.

Denny