Hi Can anyone help me here. I h

[Honyo]

Hi Can anyone help me here.

I have a SQL server (BCSQL1)that is linked to a remote server (DENSQL1). I have a query that I need to run that will go from BCSQL1 to DENSQL1, pull specific information from DENSQL1 and then insert it into a table on BCSQL1.
I need all the current days data.

Database is: AVS
Tables are: Transaction_Info and Trans_Feature_Info
Transaction_Info has a date column (stored as a string).

I need collect all the data from Trans_Feature_Info table on DENSQL1 and insert it into Trans_Feature_Info table on BCSQL1 using todays date.

Here is the query.

declare @currentyear varchar(4)
declare @currentmonth varchar(2)
declare @currentday varchar(2)

select @currentyear = datepart(Year, getdate())
if len(datepart(Month, getdate())) <2
select @currentmonth = '0' + substring(str(datepart(Month, getdate()),2),2,1)
else
select @currentmonth = datepart(Month, getdate())
if len(datepart(Day, getdate())) <2
select @currentday = '0' + substring(str(datepart(Day, getdate()),2),2,1)
else
select @currentday = datepart(Day, getdate())

INSERT INTO Trans_Feature_Info
SELECT * FROM &quot;DENSQL1&quot;.AVS.dbo.Trans_Feature_Info inner join &quot;DENSQL1&quot;.AVS.dbo.Transaction_Info

on Trans_Feature_Info.Transaction_ID = Transaction_Info.Transaction_ID

WHERE (left(Transaction_Info.Trans_Date,8) = @currentmonth + @currentday + @currentyear)

ANy help would be greatly appreciated.

Regards
Honyo

 

[LFCfan]

Hi
You didnt say what your problem was exactly, but does changing the following help?

SELECT * FROM  [DENSQL1].AVS.dbo.Trans_Feature_Info  AS tf  inner join [DENSQL1].AVS.dbo.Transaction_Info AS ti
on  tf.Transaction_ID = ti.Transaction_ID

 

[SQLSister]

You also could be having a problem by using select * becaue you have a join and thus the same field will be returned twice and will not match up to the number of fileds inthe table you are inserting into. Never use select * in an insert, you should always specify the specific columns you want and the order you want them in. If the tables happen to have the filed sin a differnt order select * might try to insert the wrong field's data into the table and you could end up with type mismatch errors.