how to generate insert data script from #temp tables?

Question

In DB I have #temp tables with data and I need to generate insert Scripts (for all data).

How it can be done ? I right clicked on tempDB and selected > Tasks > Generate Scripts but I can't select #temp tables to generate script (they are not avaialble to select).

how I can geneate Insert script from #temp tables I m using SQL Server 2008 R2.

Answer 1

You can insert your query results into sql table (temporary table , it will be created automatically ):

SELECT * INTO myTempTable FROM (query results) 

e.g : SELECT * INTO myTempTable FROM user where condition

A table named myTempTable will be created inside schema dbo Then click on database click :

Tasks > Generate Scripts

and you choose the table myTempTable

Answer 2

Another best way to do it just bit faster but longer.

Use SqlPubWiz

Just go:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4

and run and fill required information regarding log in into database and choose your database and get your whole database script and find the table's insert script that you wanted in your saved script file.

Answer 3

You can use the following query batch for generating scripts for temp tables and you can select the rows based on the conditions. I have got this from here. In the original source, the Author created Stored Procedure for generating scripts. I have formatted and modified for declaring TableName with Condition. You need to run this query batch in tempdb with your #temp table name. Thanks to Neeraj Prasad Sharma.

DECLARE @QUERY VARCHAR(MAX) = 'Dbo.#Temp where 1 = 1'

SET NOCOUNT ON

DECLARE @WithStrINdex AS INT
DECLARE @WhereStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaAndTAble VARCHAR(270)
DECLARE @Schema_name VARCHAR(30)
DECLARE @Table_name VARCHAR(240)
DECLARE @Condition VARCHAR(MAX) 

SELECT @WithStrINdex = 0
SELECT @WithStrINdex = CHARINDEX('WITH', @Query), @WhereStrINdex = CHARINDEX('WHERE', @Query)

IF(@WithStrINdex != 0)
    SELECT @INDExtouse = @WithStrINdex
ELSE
    SELECT @INDExtouse = @WhereStrINdex

SELECT @SchemaAndTAble  =  LEFT(@Query, @INDExtouse - 1) 
SELECT @SchemaAndTAble = LTRIM(RTRIM(@SchemaAndTAble))

SELECT @Schema_name = LEFT(@SchemaAndTAble, CHARINDEX('.', @SchemaAndTAble ) - 1)
,@Table_name  =  SUBSTRING(@SchemaAndTAble, CHARINDEX('.', @SchemaAndTAble ) + 1, LEN(@SchemaAndTAble))
,@CONDITION = SUBSTRING(@Query, @WhereStrINdex + 6, LEN(@Query))--27+6


DECLARE @COLUMNS TABLE([Row_number] SMALLINT, Column_Name VARCHAR(MAX))
DECLARE @CONDITIONS AS VARCHAR(MAX)
DECLARE @Total_Rows AS SMALLINT
DECLARE @Counter AS SMALLINT

DECLARE @ComaCol AS VARCHAR(MAX)
SELECT @ComaCol = '', @Counter = 1, @CONDITIONS = '' 

print @Schema_name
print @Table_name

INSERT INTO @COLUMNS
SELECT ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION) [Count] ,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema_name
AND TABLE_NAME = @Table_name 
AND COLUMN_NAME NOT IN ('SYNCDESTINATION','PENDINGSYNCDESTINATION' ,'SKUID','SALECREDITEDTO')

SELECT @Total_Rows = COUNT(*) FROM @COLUMNS
SELECT @Table_name = '['+@Table_name+']'
SELECT @Schema_name = '['+@Schema_name+']'

WHILE (@Counter< = @Total_Rows )
BEGIN 
    SELECT @ComaCol =  @ComaCol + ' ['+Column_Name+'],' FROM @COLUMNS
    Where [Row_number] = @Counter

    SELECT @CONDITIONS = @CONDITIONS+ ' + CASE WHEN ['+Column_Name+'] IS NULL THEN ''NULL'' ELSE '''''''' + 
                    REPLACE( CONVERT(VARCHAR(MAX),['+Column_Name+']) ,'''''''','''')
                    +'''''''' END +'+''','''
                    FROM @COLUMNS WHERE [Row_number] = @Counter

        SET @Counter = @Counter + 1
END

SELECT @CONDITIONS = RIGHT(@CONDITIONS, LEN(@CONDITIONS) -2)

SELECT @CONDITIONS = LEFT(@CONDITIONS, LEN(@CONDITIONS) -4)

SELECT @ComaCol =  SUBSTRING (@ComaCol, 0, LEN(@ComaCol))

SELECT @CONDITIONS =  '''INSERT INTO ' + @Schema_name + '.' + @Table_name + '(' + @ComaCol + ')' +' VALUES( '+'''' + '+' + @CONDITIONS
SELECT @CONDITIONS = @CONDITIONS + '+' + ''')'''

SELECT @CONDITIONS =  'SELECT' + @CONDITIONS + 'FROM' + @Schema_name + '.' + @Table_name + ' WITH(NOLOCK) ' + ' WHERE ' + @Condition
PRINT(@CONDITIONS)
EXEC(@CONDITIONS)

Answer 4

You have to right click on database and click on

Tasks -> Generate Scripts

now you are having a popup screen go to next click

Select specific database objects

now select your table from Tables option and click next now you have Advanced button there click on it.

You will have another small popup screen available choose Types of data to script and select

Data only

click OK and don't forget to see the path as file name where your script save carefully.

Now click Next and again Next your script is ready with the data.