Insert INTO Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert INTO Select

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2014-04-22 : 09:51:13

Hi

I have this problem to solve.

I have to get any table as input parameter, then create a temp table in the same structure as the input table parameter, excluding the primary key.

Then after load data from original table to temp table, excluding primary key as well, then update data in the temp table then load them back to the original table

this is how I approached it,
I create a temp table during a run time with Dynamic SQL:

DECLARE @SQLStatement NVARCHAR(MAX),@TableName NVARCHAR(MAX)

SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';

SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END

FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity = 0
ORDER BY column_id;

--Inserting data into temp table:
I will run another dynamic sql with this logic
INSERT INTO Hosea_tempTable
SELECT * FROM Hosea_tblDef_RETURNS

My temp table will always be one column less than the original table, because I'm omitting the primary key when I'm creating the temp table.

The problem now is inserting into temp table, because it's one column less and I'm selecting all from original table.

How can I solve this??

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-22 : 12:16:58

Use SELECT ... INTO to create the table rather than generating a CREATE TABLE statement. As part of that, you can "cancel" the identity column to instead make it a standard int column, after which you can just insert the entire table into the temp table without worrying about excluding the identity column.

stahorse
Yak Posting Veteran

86 Posts

Posted - 2014-04-23 : 02:43:16

Can you maybe show me how then do I cancel identity column to instead , I tried doing that but I'm not getting it right.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-23 : 08:35:30

I may not have understood your requirement completely, but I think what Scott is suggesting is conceptually something along these lines.

SELECT * INTO YourTempTable FROM YourOriginalTable;
ALTER TABLE YourTempTable DROP COLUMN YourIdentityColumn;
INSERT INTO YourNewTable SELECT * FROM YourTempTable;

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-23 : 10:03:57

[code]

SELECT *
INTO temp_table_name
FROM original_table
WHERE 1 = 0
UNION ALL
SELECT *
FROM original_table
WHERE 1 = 0

[/code]
The "UNION ALL" will effectively cancel the identity -- that is, SQL Server automatically removes the identity property from any column involved in a UNION statement.

Thus, you can use the:

INSERT INTO <table_name>
SELECT *
FROM data_table

and capture all columns into the temp table, including the identity value, because that column no longer has the IDENTITY property.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources