Problem with select into querry

I have two database on same server. I want write a querry,witch qering the data from 1st database, than create a new table on the 2nd database.

But the in clause after the into clause permanently generate error.

Here is the query’s little part:

Select …..

into Backup in ’Test.mdb’ from …

I try wrote other ways

in ’Test’

in ’dbo.Test’

in dbo.Test

but none of them work

its only work, when i not want pass for the second database

How can i pass the result for the 2nd Database

What database engine are you using? You're posting this in the MS SQL forum, yet I see the "Test.mdb" which looks like an Access database.

If it's MS SQL, use the syntax like:

SELECT ... INTO SERVER2.DBname.dbo.TableName

You will also need to add SERVER2 as a linked server to SERVER1 (read BOL on sp_addlinkedserver).

|||

May you post your complete SQL Statement? Perhaps you have an syntax mistake (I never user into to query a access-database).

ralph

|||

I use MS SQL 2005

here the query, sorry the column names is hungarian

the server name is sysnapsys and synapsys have the two database

select

t1a.azonosító,

t1a.nv,

t1a.lakcím_irsz,

t1a.lakcím_helysg,

t1a.lakcím_uhea,

t1a.szlacím_nv,

t1a.szlacím_irsz,

t1a.szlacím_helysg,

t1a.szlacím_uhea,

t3.telepíts_id?pontja,

t4.szerzodes_megszunt_idopont,

t9.befizetett_eloleg,

t9.havi_díj_fizetsi_mód

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

this querry is working. If i want add the in clause after the "into SzerzWorth", then the server throw me an error. I try this methods:

" into SzerzWorth in 'Teszt' "

" into SzerzWorth in 'dbo.Teszt' "

" into SzerzWorth in 'synapsys.Teszt' "

" into SzerzWorth in 'synapsys.db.Teszt' "

but permanently throw me the error:

"Msg 156, Level 15, State 1, Line 15

Incorrect syntax near the keyword 'in'. "

|||I posted above|||hi mr. saint,

a SELECT INTO <table> IN is not a correct syntax. The IN keyword expects a list(s) of values e.g: IN (1, 2, 3) and usually used in WHERE clauses to check for data w/c matches the lists in IN.i.e.: SELECT * FROM table WHERE field1 IN (n1, n2.. n2n).. the query should be:

SELECT <fields>
INTO SzerzWorth
FROM t0, t1a, t4, t9

INSERT INTO synapsys.db.Teszt (fields...)
SELECT fields..
FROM SzerzWorth

or. simply:
INSERT INTO sysnapsys.db.Teszt(fields..)
SELECT <fields>
FROM t0, t1a, t4, t9

HTH|||I don′t know if that is the problem, but as I never use umlauts or special characters in the object names you should put the names in brackets, because the syntax look quite ok:

select

t1a.[azonosító],

t1a.[nv],

t1a.[lakcím_irsz],

t1a.[lakcím_helysg],

t1a.lakcím_uhea,

t1a.[szlacím_nv],

t1a.[szlacím_irsz],

t1a.[szlacím_helysg],

t1a.[szlacím_uhea],

t3.[telepíts_id?pontja],

t4.[szerzodes_megszunt_idopont],

t9.[befizetett_eloleg],

t9.[havi_díj_fizetsi_mód]

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.