worldspawn
asked on
Create a Table variables schema from an existing table
Hello all,
I need to select some rows from a table in to a table variable. The problem I am having is that I need to define the schema for the table variable, which is essentailly the same as the table that I am selecting from. I was wondering if there was some other way of defining a table variables schema from a source, rather than hard coding it.
So something like
DECLARE @myTable table (SELECT * FROM tMyOtherTable)
would be nice
Also, using my table variable I then select a series of related data based on the pk.
So i'm doing
Select
tBooleanValues.*
FROM
tBooleanValue
JOIN tBooleanValue ON tFieldValue.FieldValueID = tBooleanValue.FieldValueID
WHERE
tFieldValue.ContainerID IN (SELECT ID FROM @myTable)
this several times. The only thing that changes is the tBooleanValue becomes tTextValue or tDateValue etc. I was wondering if there was some way I could put this in a loop and use an alias for the table that I want to select from.
Cheers
I need to select some rows from a table in to a table variable. The problem I am having is that I need to define the schema for the table variable, which is essentailly the same as the table that I am selecting from. I was wondering if there was some other way of defining a table variables schema from a source, rather than hard coding it.
So something like
DECLARE @myTable table (SELECT * FROM tMyOtherTable)
would be nice
Also, using my table variable I then select a series of related data based on the pk.
So i'm doing
Select
tBooleanValues.*
FROM
tBooleanValue
JOIN tBooleanValue ON tFieldValue.FieldValueID = tBooleanValue.FieldValueID
WHERE
tFieldValue.ContainerID IN (SELECT ID FROM @myTable)
this several times. The only thing that changes is the tBooleanValue becomes tTextValue or tDateValue etc. I was wondering if there was some way I could put this in a loop and use an alias for the table that I want to select from.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not 100% sure what your after, but would this do the trick??
DECLARE @sql NVARCHAR
SET @sql = 'SELECT * FROM tMyOtherTable'
EXEC sp_executesql @sql
DECLARE @sql NVARCHAR
SET @sql = 'SELECT * FROM tMyOtherTable'
EXEC sp_executesql @sql
ASKER
not really. no.
ASKER
The point of not using the hard coded table structure was so that any SP's that do it can cope with a table structure change without falling over.
alas it was not meant to be