Can I add a blank record to the end of my record set? 2

[T1M]

I need to create the SQL that will select all rows from a table and will add an additional empty record to the end. I will be using the results in ColdFusion (though this likely will not matter, it may be helpful information).

SELECT NAME, ID
FROM CONTACT
WHERE NAME LIKE 'T%';

What do I need to add to this to get all the names that start with T and get one extra record in my record set.

T1M

 

[Cagliostro]

As I remember in FoxPro exists an instruction append blank. In ANSI SQL is no way to do that. John Fill

ivfmd@mail.md

 

[T1M]

I think I can do this:

SELECT name, id 
FROM CONTACT
UNION
SELECT NULL as name, NULL as id 
FROM CONTACT

But, I want to do this for all columns that I select. I may be selecting 20+ columns or using "SELECT *" and if there were a way to iterate through the columns...

If anyone has a quick way to do this, it would save me a lot of time.

Thanks in advance,

Tim

 

[Tim1]

Hi Tim,

Basically, there is no way to add a blank row to the end of a table. As per John's answer, in some hierarchical databases (FoxPro, Clipper etc.) there is an APPEND BLANK command, there is no eqivalent to that in SQL.

More importantly, before you can append a blank row, what you need to do is check that the table definition is such that all columns will:

a) Accept a null value
b) Not have bound default values that will automatically put data into the table for you.

If both of these are set so that you can have a completely blank row, and to be honest, it will be a very unusual RDBMS table that will allow you to do so, then you can append your blank row to the end, but it will have to be in the form:

INSERT table (column1, column2, .....)
VALUES (NULL, NULL, .....)

I am afraid that there is no quicker way that this in SQL, it is not really designed to allow this kind of thing to happen.

Tim

 

[T1M]

Thanks for your help, Tim.

I am going to have to do some work in my application design rather than on my data to find an answer to my problem.

Tim

 

[vtj]

Tim,

If your table always has the same structure, you could predefine a "blank" record in a second table and insert that record into your database. Using order by you should be able to get the record to the bottom. If you run interactive SQL you can change the session attributes to append to current member or using the previous union example replace the second select clause with

SELECT FLD1, FLD2, ... ,FLDn FROM DUMMYREC

Where DUMMYREC is your second table.

I hope this helps.

vtj
VTJ