Copy an existing table to a new table...

Question

Post reply

Copy an existing table to a new table...

stephen.aa

SSC Eights!

Points: 815
More actions
August 24, 2021 at 4:10 pm

#3923561

None of this works:

USE DriveWorks_Data
CREATE TABLE 2415_10_Ga_Trunking_Design_Configurator LIKE 2415_12_Ga_Trunking_Design_Configurator
SELECT * INTO 2415_10_Ga_Trunking_Design_Configurator FROM 2415_12_Ga_Trunking_Design_Configurator
CREATE TABLE 2415_10_Ga_Trunking_Design_Configurator SELECT * FROM 2415_12_Ga_Trunking_Design_Configurator

And this also does not work:
https://docs.microsoft.com/en-us/sql/relational-databases/tables/duplicate-tables?view=sql-server-ver15

It places all the definitions into the top-left cell.

All I want to do is create a new table with the same structure as an existing table, in the same database.

Thanks!
Steve Anderson

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

right-click on the table, and "script table as"... then "Create to..."?

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

stephen.aa wrote:

None of this works:
...
SELECT * INTO 2415_10_Ga_Trunking_Design_Configurator FROM 2415_12_Ga_Trunking_Design_Configurator

What about that doesn't work?

If you don't want any data in the table, use TOP (0) and/or WHERE 1 = 0 to create an empty table with the same structure:

SELECT TOP (0) *

INTO 2415_10_Ga_Trunking_Design_Configurator

FROM 2415_12_Ga_Trunking_Design_Configurator

WHERE 1 = 0

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

In SQL Server, you have a couple of things working against you.

A table starting with a numeric digit is against the "normal naming convention" for objects in SQL server. It you need to do so, you must encapsulate the name in brackets.

Although not a show stopper, it's better for performance and does add a bit of bullet proofing to you code if you use the 2 part naming convention of schemaname.objectname in T-SQL (SQL Server).

There is no CREATE/SELECT sequence in T-SQL. There IS a SELECT/INTO sequence, though. Like this...

 SELECT * 
   INTO dbo.[2415_10_Ga_Trunking_Design_Configurator] 
   FROM dbo.[2415_12_Ga_Trunking_Design_Configurator]
;

This will create the new table with the same datatypes and an IDENTITY (if one exists) and the nullability of each column. It will not copy indexes or constraints or computed column formulas. Computed columns will be materialized as real columns. The resulting datatype can be quite surprising to the uninitiated.

Here's the link to the MS documentation.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver15

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

also, if you copy a very large table, like the one with millions or dozens of millions rows and a lot of columns, you may want to do it in a LOOP in batches of , for example, 10 000 rows at a time. Not all as one large Insert/Select transaction. Otherwise your table will be inaccessible for quite some time and you risk to even hang the server or stress the Transaction Log too much.

Likes to play Chess

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

Once you generate the script to create the new table, you can use this command to move the data. No logging, almost instantaneous.

ALTER TABLE Source
SWITCH TO Destination

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

@stephen.aa ,

The bottom line here is that it looks like you might be coming to SQL Server from Oracle and I'll tell you that SQL <> SQL and, for sure, PL/SQL <> T-SQL.

If you'd post the CREATE TABLE statement for the original table along with the code for the PK and Clustered Index (they could be different) and provide some information about the number of rows, whether the source table is in use during the creation of the target table, and a couple of other things that might strike you as being of interest, we can show you a few ways to do what you want/need while taking things into consideration like concurrency, resource usage, log file usage, etc, etc.

All we have from you so far is a little information about your incorrect code and the resulting failed attempt.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)