Create a table from select statement

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 2005 Forums

Transact-SQL (2005)

Create a table from select statement

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-09 : 14:00:29

Is it possible to create a SQL temporal table from a select statement and do not need to add column by bolumn?

For example, a select statement like:

Select * from tblOrder (create a table including all columns)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-09 : 14:06:26

Something like this:
SELECT *
INTO #MyNewTable
FROM SourceTale

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 14:09:17

For only structure you can use :

Select * into #temptable from table where 1 = 0

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-09 : 14:18:39

Can I do it like below? In real code, there is no such table "tblOrder". It is more complicate statement.

SELECT *
INTO #MyNewTable
FROM (Select * from tblOrder )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 14:23:47

Select top 0 * into #temptable from table

E 12°55'05.25"
N 56°04'39.16"

buzzi
Starting Member

48 Posts

Posted - 2008-07-09 : 15:57:24

yes, you can use complicated queries as you want, you dont have to use a "SELECT" keyword again after the FROM clause

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-09 : 17:23:56

You can use joins & the WHERE clause to create the structure & data in the table that you want. eg
SELECT tblOrder.delivery_address_city, tblOrderLineItem.product_quantity, tblProduct.product_name
INTO #YourNewTable
FROM tblOrder
JOIN tblOrderLineItem ON tblOrder.order_id = tblOrderLineItems.order_id
JOIN tblProduct ON tblOrderLineItem.product_id = tblProduct.product_id
WHERE tblOrder.delivery_address_state = 'Maharastra' and delivery_address_country = 'India'

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2008-07-10 : 11:02:49

I tried it but got error. (@sql will be a string passed from program:
like "from authors", If replace @sql with "from authors", no error )

CREATE PROCEDURE spQ_make_temp
@sql varchar(800)
AS

begin

If Object_Id('tempdb..#MyTemp') is not null
begin
Drop Table #MyTemp
End

select top 0 * into #MyTemp @sql

insert into #MyTemp select * @sql

END
GO

buzzi
Starting Member

48 Posts

Posted - 2008-07-10 : 18:43:46

you need to use dynamic sql:
something like this should work

alter PROCEDURE spQ_make_temp
@sql varchar(800)
AS

begin

If Object_Id('tempdb..##MyTemp') is not null
begin
Drop Table ##MyTemp
End
declare @sql1 nvarchar(800)
set @sql1 ='select top 0 * into ##mytemp ' + @sql
exec sp_executesql @sql1
set @sql1 ='insert into ##mytemp select * ' + @sql
exec sp_executesql @sql1

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources