T-SQL Scripting data and schema from table variable or temp table

Question

Using SQL Server 2012. I am aware of the ability in SSMS to script schema & data to a query window, file, etc.

Right click on database, Tasks --> Generate Scripts --> Choose Objects --> Advanced Scripting Options --> General --> Types of data to script --> Schema and Data (Schema Only/Data Only)

Is there a way to do this on a table variable or #temp table without using a third-party product? (and/or script the Schema, Data, Schema and Data, as in the Scripting menu).

I was able to do this using a third-party product, in which I ran the query to select to grid, and then scripted the results. But, I'd like to be able to do this without this product if possible.

My test query:

/*  
CREATE VIEW dbo.vRandomNumber 
AS 
SELECT RAND() AS RandomNumber 
GO

CREATE FUNCTION dbo.UDF_Q_RandomNumber()
RETURNS NUMERIC(18, 10)
AS
BEGIN
    RETURN (SELECT TOP 1 RandomNumber FROM vRandomNumber)
END
GO
*/

declare @t_testTable TABLE (
    [nbr] int,
    [InfoField] [nvarchar](max) NULL,
    [AnotherInfoField] [nvarchar](max) NULL,
    [description] [nvarchar](max) NULL,
    [Rnum] int,
    [TransDate] datetime
    );

declare @v_dt datetime;
set @v_dt = getdate();

insert into @t_testTable (
    [nbr],
    [InfoField],
    [AnotherInfoField],
    [description],
    [Rnum],
    [TransDate])
VALUES (1,'Info1','Info 1 Field','Test Field - info 1', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (2,'Info2','Info 2 Field','Test Field - info 2', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (3,'Info3','Info 3 Field','Test Field - info 3', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (4,'Info4','Info 4 Field','Test Field - info 4', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (5,'Info5','Info 5 Field','Test Field - info 5', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (6,'Info6','Info 6 Field','Test Field - info 6', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (7,'Info7','Info 7 Field','Test Field - info 7', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (8,'Info8','Info 8 Field','Test Field - info 8', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (9,'Info9','Info 9 Field','Test Field - info 9', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt),
       (10,'Info10','Info 10 Field','Test Field - info 10', cast(1000 * dbo.UDF_Q_RandomNumber()+1 AS INT),@v_dt);


    select * from @t_testTable

a temp table is really a `temp`. It's like your temporary internet files generated by your browser. What makes it beneficial for you to have a copy of that temp data? — techspider, Oct 17 '16 at 19:53
Just get the structure of the table (temp or variable). Create table with this structure. Put the data in the new table. Export it (if you do not want to use the menu, check the `bcp` command. — gotqn, Oct 18 '16 at 06:30
Thank you @gotqn. Also, techspider & alroc. That is what I thought, as well. I was just wondering if there were a way to bypass the steps (creating a table & then exporting it) that I was not aware of. Since a third party tool was able to take the result set & export them, I asked the question in case there was a way to do it in SSMS that I wasn't aware of. Thanks, each of you. — steve_o, Oct 18 '16 at 12:27

T-SQL Scripting data and schema from table variable or temp table

0 Answers0