August 24, 2021 at 4:10 pm
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:
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
August 24, 2021 at 5:04 pm
right-click on the table, and "script table as"... then "Create to..."?
August 24, 2021 at 6:34 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 25, 2021 at 3:21 am
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.
--Jeff Moden
August 25, 2021 at 12:39 pm
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
August 26, 2021 at 1:41 am
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/
August 26, 2021 at 4:00 am
@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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply