DBA > Job Interview Questions > Sybase Interview Questions and Answers

How do I exclude a column in Sybase ?

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How do I exclude a column in Sybase ?

Open/Client 11.1.1
Create a view based on the table that you want to exclude a column from and then bcp out from the view. Open/Client Versions Older Than 11.1.1

The documentation Utility programs for Unix describes the use of format files, including the field Server Column Order. Server Column Order must equal the colid of the column, or 0 if the host file field will not be loaded into any table column.

I don't know if anyone has got this feature to work. So, here is another way of removing the column. In your example, you want to remove the last column. I am going to include another example to remove the second column and include a fourth column. Why? Because it is harder. First example will deal with removing the last column.

Removing the Last Column
Edit your bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data:
--- bcpout.fmt
10.0
2 <------------------ Changed number of columns to BCP to two
1 SYBINT4 0 4 "<**>" 1 counter
2 SYBCHAR 1 512 "\n" 2 text1 <--- Replaced <**> with \n
3 SYBCHAR 1 512 "\n" 3 text2 <--- DELETE THIS LINE

Now recreate the table with the last column removed and use the same bcpout.fmt file to BCP back in the data.
; Now let's try removing the second column out four columns on a table. Removing the Second out of Four Columns
Edit the bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data:
; --- bcpout.fm
10.0
3 <------------------ Changed number of columns to BCP to three
1 SYBINT4 0 4 "<**>" 1 counter< 2 SYBCHAR 1 512 "<**>" 2 text1 <--- DELETE THIS LINE
; 2 SYBCHAR 1 512 "<**>" 3 text2 <--- Changed number items to
3 SYBCHAR 1 512 "\n" 4 text3 <--- Changed number items to 3
; Including the Fourth Column

Now copy the bcpout.fmt to bcpin.fmt, recreate table with col 2 removed, and edit bcpin.fmt file:
;
; --- bcpin.fmt
; 10.0
; 3
; 1 SYBINT4 0 4 "<**>" 1 counter
; 2 SYBCHAR 1 512 "<**>" 2 text2 <-- Changed column id to 2
; 3 SYBCHAR 1 512 "\n" 3 text3 <-- Changed column id to 3

(Continued on next question...)

Other Job Interview Questions