Declare @Variable LIKE Table

[Gixonita]

Hi Everyone

I have 2 tables, one contains client admissions and the other client discharges, both tables have a lot of columns. This is an old system that has been refined over time and the problem I'm working on requires to create a discharge for every client that has had no services in a certain amount of time. For this I'm planning on doing:

1.- Create Cursor to select clients with no services in amount of time (and no discharge).

2.- For each row in cursor

2.1 Generate discharge
2.2 Get the @@Identity
2.2 Link admission with the newly created discharge (@@Identity)

Both tables have close to 200 columns and are basically the same information (like mental health status at admission and mental health status at discharge), so basically I'm "copying" the data in admission to generate the discharge.

When I worked in Informix I remember there was a way to declare a record variable that would contain all the columns of the table:

Define VARIABLE LIKE TABLE.*

So basically you don't have to declare every single column as a separate variable and the fetch would go like:

Fetch INTO VARIABLE.*

Instead of

Fetch INTO Col1,Col2,.....ColN

I've been searching for a while and I can't seem to find something similar in TSQL. Do I really have to declare 200 variables for the fetch or is there something like the declare in Informix?

Thanks in advance for your help

Luis Torres

 

[bborissov]

Can't you do this w/o cursors? Something like:

SELECT *, IDENTITY(int, 1, 1) AS Discharge
       INTO #Test
       FROM .....
       WHERE ....

INSERT INTO OtherTable
SELECT * FROM #Test
DROP TABLE #Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

And never ever use @@identity, use scope_identity() instead or you will have data intregrity problems if someone puts a tirgger on your table tha inserts into another table with an idenity. @@identity is just plain unsafe to use ever (unless you are SQl Server 6.5 which has no scope_identity())

If you are looking for a table variable then the syntax is
something like:

Declare @temptable Table (field1 int, field2 varchar(30))

If you have a natural key in your data you don;t need to do this in a cursor (cursors are notorious performance killers and should be avoided if at all possible.) Then you get the new identity for each discharge by querying the natural key. For instance, If I was to do a admit, discharge sequence, I would store the id field for the admission in the table for the discharge to make it simple to link the two in reporting. In this case I would find the ids for the discharges by (assuming the records I was going to create discharges for were in the table variable @temp

select discharge_id, d.admit_id from discharge d join @temp t on d.admit_id = t.admit_id

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Gixonita]

Hi bborisov and SqlSister

I apologize for not responding yesterday, after I wrote this message we went into "crisis" mode with some user requests and I couldn't check the forum again. Thank you for your suggestions they game me ideas as to solve the problem

Have a great day

Luis Torres

 

[SISlayer]

I also searched for a long time, but it realy seems, that there is no way to FETCH a(the next) row into a TABLE or a TABLE variable. Can this realy be? - The thing with the IDENTITY column is nice, but what do i do if i dont have such a column!? This problem rises the affort writing a work around (like defining each variable seperatly) enourmous

 

[SQLSister]

SISLayer, you really shouldn't be fetching individual rows into a table or table variable, you should be bringing in sets of data. If you are using cursors, then the thing to do is to stop using them.

Also your data should be structured if at all possible to have either a natural key (data fields that together will naturally make up a unique record or a single field which is guaranteed to be unique)) or an identity or both. One lesson I learned a long time ago is never put data into a table that doesn't have some way to uniquely identify a record. It's a structural problem which needs to be fixed before you do anything else or you will have no end of data integrity and query writing problems.

If you havea specific problem that you are trying to solve, please put it into a separate thread of it's own.

Questions about posting. See faq183-874