Create @TableVariable based on an existing database table?

Question

I want to use table variables in stored procedures but here is an issue. My tables are very large and declaring a table variable need a long code to write and debug as well.

Kindly advice me some way to declare table variables quickly, is it possible to create table variable based on an existing table ?

Or please share any tip to create code for creating table variable.

Thanks

Solution 1

As discussed in this SO Question you can't select into a table variable.

When you say "large", if you mean a lot of columns, the best approach for you would probably be to script that table as create and save the definition and use that in your Declare statement.

If you mean large as far as the number of rows you'll have in the table variable, you may want to consider using a temporary table which you could then do a SELECT INTO statement to create it based off of the original.

SELECT * INTO #tmpTable FROM srcTable

Solution 2

Right click the table, choose Script As Create.

Replace create table xxx with declare @xxx table.

Solution 3

The simple answer is "No you cannot create a variable table based on other table"

But, you can generalise a bit by using a type table. For example (note: you can add documentation to the type table and columns, which can be useful for future reference):

PRINT 'type: [dbo].[foo_type]'
PRINT ' - Check if [dbo].[foo_type] TYPE exists (and drop it if it does).'
GO
IF EXISTS (SELECT 1 FROM sys.types WHERE name = 'foo_type' AND is_table_type = 1 AND SCHEMA_ID('dbo') = schema_id)
BEGIN
    -- Create the proc
    PRINT ' - Drop TYPE [dbo].[foo_type]';
    DROP TYPE [dbo].[foo_type];
END;
GO
PRINT ' - create [dbo].[foo_type] TYPE.'
GO
CREATE type [dbo].[foo_type] as Table
(
        [id]                    int identity(1,1) PRIMARY KEY
        , [name]                varchar(255) NOT NULL
        , [description]            varchar(255)
        , numeric_data            numeric(26, 6)
        , datetimestamp            datetime default getdate() 
        , Unique_Indicator        float unique not null default cast(getdate() as float)
        , CHECK (Unique_Indicator > 0)

);
GO
PRINT ' - done.'
GO


-- Adding the descriptions
PRINT ' - Adding Type level Description'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'describe the usage of this type.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'foo_type'
GO
PRINT ' - Adding Column level Descriptions'
PRINT '   - column: id'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the record...' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'foo_type', @level2type=N'COLUMN',@level2name=N'ID';
GO

------------------------------------------------------------------------------------------------
-- use the type defined above to manipulate the variable table:

declare @foo_table foo_type;

--insert using the default value for the for the unique indicator.
insert into @foo_table (name, [description], numeric_data, datetimestamp)
    values('babar', 'this is the king of the elephants', 12.5, '1931-01-01')
        ;

-- insert the records one by one to use the scope_identity() for the unique indicator.
insert into @foo_table (name, [description], numeric_data, datetimestamp, Unique_Indicator )
    values('zephyr', 'Babar''s monkey friend', 5.5, '1932-01-01', scope_identity())
        ;
insert into @foo_table (name, [description], numeric_data, datetimestamp, Unique_Indicator )
    values ('Celeste', 'Babar''s sister', 19.5, '1932-01-01', scope_identity())
        ;

-- insert using a list of values
insert into @foo_table (name, [description], numeric_data, datetimestamp, Unique_Indicator )
    values('Babur', 'Not Babar!!!', 1483, '1983-02-14', 10)
        , ('Mephistopheles', 'Not Babar either...', 666, '1866-01-01',11)
        ;

-- insert using a select
insert into @foo_table (name, [description], numeric_data, datetimestamp, Unique_Indicator)
    (select 'Conan', 'The Cimmerian barbarian', 850, '1932-12-01',99 union
        select 'Robert E. Howard', 'Conan''s creator', 30, '1906-01-22', 100
    );

-- check the data we inserted in the variable table.
select * from @foo_table;


-- Clean up the example type
DROP TYPE [dbo].[foo_type];

Comments

[haansi]

thanks, can we create table variable based on existing table ?

[Mike Walsh]

@haansi What Andomar described (and what I described) is how to do it. As I said in my answer, you can't do a select into to create it from an existing table like you could do for a temp table. Scripting it out and using it for your declare is the only real way.

[Shamim]

But what if there is a change in table structure?