Exclude Columns when select * into table. how?

[besabe]

I need to create a dynamic sp in order to create temporal tables using the EXEcuted statement creating a string cmd:

exec @cmd = ('select * into from' + @tabname)

But I need to exclude some columns to not include into the new temp table. I already have a TableList in wich I specify the Exclude column names. For example, I have a table with 100 columns, however I need only 90 columns, my idea is to exclude columns instead of specify the 90.

Any idea or another way to do it?...

 

[JeanNiBee]

Hi

Two ways you can do this.

Specify the column list instead of *, this works assuming the columns are the same name in the source (From table) and destination (into table).

Or, look at your solution in reverse and use the following:

INSERT INTO Table1 VALUES (col1, col2, col3) SELECT orig_col1, orig_col2, orig_col3 FROM SomeOtherTable



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[besabe]

Hello JeanNiBee,

Thanks for you response. What I want it is more dynamic. I know that I can specify the column list instead of the *. But the way that it try to do, is based on a exclude column list. I want the sp to write for me automatically the select list. For example, I can go to syscolumns and identify the exclude columns, based on that; I can create a string with all the valid columns. The problem that I have right now is that the string is too long and I can't run the exec statement, another way that I found is to drop the columns but since this sp it for the end-user, there is no right to alter table.

string "select col1,col1,col3............col100"

P.D sorry for my English, my first language is Spanish...

 

[JeanNiBee]

Hi

This is definately a problem for the exact reason you mention. There is a limit to the length of the string (limited by the pagesize setting in your server) It can be as big as 16k now if you are using 12ASE .5, but you STILL have a limit.

I do not know how you would accomplish this directly on the server without using a programming language in between like Perl, PHP or my fav Java. Then your script can make the appropriate select statement and omits the columns it's wants by being respinsible for creating the select or insert command.


By the way, your english is very good so don't worry.






-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

My question to you.

Why not explain WHY you want to omit columns dynamically and maybe I can suggest an alternative.

Thanks.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[besabe]

Hello JeanNiBee,

Let me explain. I have a list of tables that need to be imported every day. Im using the BCP utility and then export the data into Oracle. Not all the columns are required to be imported. In order to use the BCP I have 2 options:
1: Create the tables with the columns required or
2: Create a view for each table.

I choice option 1 because I don’t want to create views for each table I prefer to do this more dynamic with a single stored procedure. I create a MasterTableList in which I list the Table Names and the select column list or the exclude column list. For example:

TableName SelectList ExcludeList
Table1 Col1,Col2 Null
Table2 Col1,Col2,Col3 Null
Table3 * Col99,Col100

Instead of type the 98 columns, I just exclude Col99 and 100.

Base on this table I go to sysobjects and create the select string.But the string for this 98 columns is to large.

Any suggestions?.. I using ASE 12.5

where you from?

 

[JeanNiBee]

I am at a loss to suggest a solution.

I even tried doing something with a variable as a 'text' datatype but you can't define them in a procedure.. how much of a pain is that.

:/

I will ask my co-workers and see if one of them has any ideas, they are quite good at fixing these kind of quirky problems.

I'll get back to you ASAP.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[besabe]

Hi,

I also did it, "text" dtatype is not valid in the sp.

what ever ideas is good to know.

Saludos

 

[JMCraig]

You flat out can't do this in a stored procedure. There's no way to create "dynamic" SQL in a pre-compiled object like a stored procedure. In point of fact, there's no way to take a constructed string value and invoke it. There simply is no function/operator/whatever to do such a thing.

You're stuck with SQL views (whose construction you could automate to some degree) or using a full-blown programming language. It's rather unclear why you need something more "dynamic" than a SQL view. Perhaps you could maintain the table definition and its companion extraction-to-Oracle view in the same file. But, and here's where the news gets worse, I don't believe you'll find that you can use BCP to extract data from a SQL view anyway. So what you might need would be a bulk-copy-from table that contains only the columns of interest (truncate it when you're done moving data).

One thing that may help you remember the limitations of stored procedures is that every table name and column name that appears in a stored procedure must be represented in the compiled code by that table & column's ID from (respectively) sysobjects & syscolumns. Or, to slightly oversimplify, this is the whole point of a stored procedure: to avoid the cost of compilation and optimization on a query that otherwise would be resubmitted each time.

So, if you can't type in the SQL as a (relatively) simple query, you can't do it in a stored procedure. You can't generate SQL which you then invoke within a procedure. Just as you can't do this by hand without taking the results of a query and pasting them back into the query window and invoking it yourself.

Another approach you might consider is that Oracle can read directly from Sybase with the appropriate middle-ware. That might be the most effective approach in the long term. Sybase could also insert the data directly into Oracle's tables (another fairly simple option).

At any rate, sorry if this is discouraging, but the approaches you have in mind are not possible options.

Best of luck,

J M Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

nsjmcraig@netscape.net

 

[besabe]

Hello JMCraig,

Thanks for your response, you not help so much.
In fact I already discard the middle-ware, for cost reason.
And yes, there is a way to create "dynamic" sql, I did it and works fine, my limitation it’s the 255 char in the string definition.
FYI, Bcp utility works for tables and views. You don’t need to create a specific table that contains only the columns required for BCP, instead you can create a personalized view and BCP out.