1

I need to make a provisionary table (TAB_PROV). This table will have its origin in a main table (TAB_MAIN). I need everything from the main table, except the data (rows).

I searched for some examples and none of them worked for me

CREATE TABLE TAB_PROV LIKE TAB_MAIN

CREATE TABLE TAB_PROV AS TAB_MAIN
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Heitor Badotti
  • 167
  • 10
  • 1
    Does this answer your question? [How to create a table from select query result in SQL Server 2008](https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008) – sticky bit Feb 12 '21 at 01:19
  • Yes, it worked!!! I made an adaption, I used "SELECT * INTO TAB_PROV FROM TAB_MAIN WHERE 1 = 0" – Heitor Badotti Feb 12 '21 at 01:33
  • Specify precisely what "everything" means to you. Permissions? Indexes? Filegroup? Primary key? etc. Usually it is a good thing to **avoid** duplication. – SMor Feb 12 '21 at 01:34

2 Answers2

1

You can just simply do:

SELECT *
FROM TAB_MAIN
INTO TAB_PROV 
WHERE 1 = 2

Since the WHERE condition will never be true, no data is ever copied - but the table structure is replicated - TAB_PROV is created and has the same column as TAB_MAIN. This does NOT however copy any constraints (check or default constraints) or triggers over - it only recreates the columns (and their datatypes).

If you want a real and complete "copy" of your table, then you should use the "Script Table" function in SSMS to get the SQL needed for TAB_MAIN and then adapt it to create TAB_PROV from it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

In SQL Server Management Studio, you can right-click the table and select

Script Table as -> Create To -> New Query Editor Window

This will create just the table creation script for you.

You can also try the code below but it will copy everything.

SELECT * 
INTO NewTable 
FROM OldTable

TRUNCATE TABLE NewTable
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
xAFGuy
  • 1
  • 1