Error-Into Clause is not allowed in openqery

Question

Post reply

Error-Into Clause is not allowed in openqery

cljolly

Ten Centuries

Points: 1179
More actions
November 11, 2011 at 10:31 am

#262652

CODE
SELECT *
FROM OPENQUERY(test_TERADATA,
'select *
into #tlb_testfile
from databse.file
where
datacode=''49548''
;')
This code runs fine without the "into #tlb_testfile" line, but when I add the code I get the error:
[NCR][ODBC Teradata Driver][Teradata Database] Syntax error: INTO clause is not allowed. ".
I really need the temp table. What am I doing wrong?
I tried searching the internet, and couldn't find much about this error
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the INTO belongs outside of the openquery:

CODE

SELECT *

into #tlb_testfile

FROM OPENQUERY(test_TERADATA,

'select *

from databse.file

where

datacode=''49548''

;')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cljolly Ten Centuries Points: 1179 More actions · Answer 2

Thank you so much. Now if I can piggyback a followup question about the same temp file...

I wanted to pull a list of column names from my temp file (and others as well and found the following code:

SELECT column_name 'Column Name',

data_type 'Data Type',

CHARacter_maximum_length 'Maximum Length'

FROM information_schema.columns

WHERE table_name = '#tbl_temptable'

When I run the code, it brings up an empty table with column headers:

Column Name, Data Type, and maximum length which coincidently is the as statements in the code. Can you tell me what I am doing wrong here?

Thanks again

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

cljolly (11/11/2011)
Thank you so much. Now if I can piggyback a followup question about the same temp file...
I wanted to pull a list of column names from my temp file (and others as well and found the following code:
SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = '#tbl_temptable'
When I run the code, it brings up an empty table with column headers:
Column Name, Data Type, and maximum length which coincidently is the as statements in the code. Can you tell me what I am doing wrong here?
Thanks again

'information_schema.columns' returns one row for each column that can be accessed by the current user in the current database. Whereas #Table sits in TempDB database.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

cljolly (11/11/2011)
Thank you so much. Now if I can piggyback a followup question about the same temp file...
I wanted to pull a list of column names from my temp file (and others as well and found the following code:
SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = '#tbl_temptable'
When I run the code, it brings up an empty table with column headers:
Column Name, Data Type, and maximum length which coincidently is the as statements in the code. Can you tell me what I am doing wrong here?
Thanks again

Empty Table / No rows returned?

cljolly Ten Centuries Points: 1179 More actions · Answer 5

Column_name Data_type Maximum Length

and the rest of it is blank.

Is there a way of displaying column names and data types from a table?

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

cljolly (11/11/2011)
Column_name Data_type Maximum Length
and the rest of it is blank.
Is there a way of displaying column names and data types from a table?

I am not sure. Try this.

USE TEMPDB

GO

SP_HELP '#TBL_TEMPTABLE'

OR

USE TEMPDB

GO

SELECT COLUMN_NAME 'COLUMN NAME',

DATA_TYPE 'DATA TYPE',

CHARACTER_MAXIMUM_LENGTH 'MAXIMUM LENGTH'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = '#TBL_TEMPTABLE'

Lowell SSC Guru Points: 323518 More actions · Answer 7

i think the issue is related to teh name not being #tbl_temptable, but actually [#tbl_temptable[100underscores]0000000000F0]

for example. if you do this after you know your table exists :

SELECT table_name,

column_name 'Column Name',

data_type 'Data Type',

CHARacter_maximum_length 'Maximum Length'

FROM tempdb.information_schema.columns

you will see the table name is not equal.

--edit--thois will get you what you are asking for:

SELECT table_name,

column_name 'Column Name',

data_type 'Data Type',

CHARacter_maximum_length 'Maximum Length'

FROM tempdb.information_schema.columns

WHERE table_name IN( SELECt name from tempdb.sys.tables WHERE object_id = object_id('tempdb..#tbl_temptable') )

the way i've always done it is with the SQL tables, and not the information_schemas:

select colz.name ,

type_name(colz.system_type_id),

colz.max_length,

*

from tempdb.sys.tables tabz

inner join tempdb.sys.columns colz

on tabz.object_id=colz.object_id

and tabz.object_id = object_id('tempdb..#tbl_temptable')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cljolly Ten Centuries Points: 1179 More actions · Answer 8

cljolly

Ten Centuries

Points: 1179

November 14, 2011 at 11:18 am

#1407433

Thank you!