How to Create Table Using Select Query in SQL Server

How to create a table from select query result in SQL Server 2008

Use following syntax to create new table from old table in SQL server 2008

Select * into new_table  from  old_table 

Create table as select causes error in SQL Server

SELECT * INTO New_Table 
FROM YourTable

How to create table using select query in SQL Server?

An example statement that uses a sub-select :

select * into MyNewTable
from
(
select 
  * 
from 
[SomeOtherTablename]
where 
  EventStartDatetime >= '01/JAN/2018' 
)
) mysourcedata
;

note that the sub query must be given a name .. any name .. e.g. above example gives the subquery a name of mysourcedata. Without this a syntax error is issued in SQL*server 2012.

The database should reply with a message like:
(9999 row(s) affected)

Create a table with SQL from Query result in SSMS

Try this:

SELECT * 
INTO NEW_TABLE
FROM table1
WHERE code = 'x'
ORDER BY code;

Create table using select query in SQL Server

I had to alias the columns in the select, but I would have to think your current query aliases the fields if they don't have name.

select
  *
into #tempabc
from (
  Select 'a' [test]
  union
  Select 'b' [test]
) a

select * from #tempabc

What is the right syntax to create a table in sql server based on SELECT query

You just need to add a INTO at the end of the SELECT part to indicate that you want to create a new table with the result of that query.

  SELECT 
      [myDB].[dbo].[intrafreq_cell].[st_umts_hua_intra_relation_key]
      ,[myDB].[dbo].[umts_df_relation].[cell_name]
      ,[myDB].[dbo].[umts_df_relation].[n_cell_name]
      ,CASE WHEN [myDB].[dbo].[umts_df_relation].[st_umts_df_relation_key] IS NULL THEN 'FALSE' ELSE  'TRUE' END AS missing_rel_in_df
  INTO MY_NEW_TABLE     
  FROM [myDB].[dbo].[intrafreq_cell] 
       LEFT OUTER JOIN [myDB].[dbo].[umts_df_relation] ON [myDB].[dbo].[intrafreq_cell].[st_umts_hua_intra_relation_key] = [myDB].[dbo].[umts_df_relation].[st_umts_df_relation_key] 
  WHERE [myDB].[dbo].[umts_df_relation].[st_umts_df_relation_key] = 'FALSE'

PS: remember to set the name "missing_rel_in_df" for your calculated column, so that column will have this name on the new table.

create table using select statement in place of table_name

To do this, you need to use dynamic sql. A quick and dirty example is:

create table test(id smallint, name varchar(15));
insert test (id, name) values
(98, 'harsh'), (78, 'Vishal'), (23, 'Ivan'), (34, 'Hardik');

declare @sql nvarchar(200);

set @sql = N'create table B' + format((select max(id) from test), 'D3')
+ N'(
    id int,
    name varchar(50)
);'

select @sql;
exec(@sql);
exec('select * from B098');

Notice that I had to resort to dynamic sql to actually use that table within the same batch. As the others have suggested, you should reconsider the path you have chosen for many reasons. Perhaps foremost is that this requires a rather advanced level of skill - you will likely need much help to make use of your table. You should consult with your DBA to get their opinion (and permission).

Creating table using select statement from multiple tables

For SQL Server you can do SELECT * INTO. Something like this:

SELECT
    *
INTO Statistics
FROM (
    SELECT 
        city.city_ID, 
        city.name as "city_name", 
        (SELECT COUNT(*) FROM person WHERE person.city_id = city.city_id) as 'number_of_births'
    FROM city
        inner join person on city.city_id = person.city_id
) t1

How do I create a table based on another table

There is no such syntax in SQL Server, though CREATE TABLE AS ... SELECT does exist in PDW. In SQL Server you can use this query to create an empty table:

SELECT * INTO schema.newtable FROM schema.oldtable WHERE 1 = 0;

(If you want to make a copy of the table including all of the data, then leave out the WHERE clause.)

Note that this creates the same column structure (including an IDENTITY column if one exists) but it does not copy any indexes, constraints, triggers, etc.

---

Related Topics