0

I wish to duplicate a table without contents (values) but keeping primary key and all the constraints.

The following code will only copy the columns' names but the primary key and constraints are missed:

SELECT TOP(0) * INTO new_table FROM source_table;

And this code will gives error about the "LIKE" syntax:

CREATE TABLE new_table LIKE source_table;

I wish to do it with a query/code, not with mouse and settings of Microsoft SQL Management Studio.

Iraj
  • 319
  • 3
  • 17
  • 1
    `create table ... like` isn't supported in SQL Server, it's a Postgres thing, that's why you're getting a syntax error. – Mike G Jun 17 '20 at 13:51
  • 1
    There is no simple way to do this in T-SQL. It is of course possible to write code that generates the `CREATE TABLE` based on metadata, but doing so is far from trivial. – Jeroen Mostert Jun 17 '20 at 13:52
  • 1
    This post on dba.se might be useful https://dba.stackexchange.com/q/18059/28164 – Mike G Jun 17 '20 at 13:52
  • @Jeroen Mostert Thank you. Yes, sadly it seems like it doesn't have a simple solution and I really need this to be done. – Iraj Jun 17 '20 at 14:14
  • @MikeTheLiar Thank you for your answer. But that solution is like my first block of code, it doesn't duplicate primary key. Also I wish to do it programmatically so that later I can copy it in C#. – Iraj Jun 17 '20 at 14:16
  • Depending on precisely what it is you're trying to do, creating the table once in advance and using `ALTER TABLE .. SWITCH` or `sp_rename` may work, or `DBCC CLONEDATABASE` for an entire DB. Beyond that I would strongly advise you not to go for a T-SQL solution as, aside from complexity, they tend to be very brittle, easily missing edge cases and advanced options. For client-side options there's SQL Server Management Objects and DacFx, both of which can generate scripts based on existing objects. Both have a learning curve, though, especially DacFx (but it is very powerful and complete). – Jeroen Mostert Jun 17 '20 at 14:19
  • Another potential option is to use a table type (`CREATE TYPE ... AS TABLE`) and declare instances of it as necessary. You cannot easily copy these either, but at least they allow you to specify nullability of the columns and (intra-table) constraints. – Jeroen Mostert Jun 17 '20 at 14:21
  • @Jeroen Mostert, I need to dynamically create new tables on occurrence of some events. My main code is in C#. What I want to do exactly is to duplicate the existing table to have the original version as a backup and then modify the duplicate table (adding some columns and values). I can do it by playing with "string query" in C# but I thought it will be nicer to do directly in SQL query. It's still strange that SQL can't do such a simple thing. – Iraj Jun 17 '20 at 14:55
  • 1
    T-SQL (and really also ANSI SQL) bank heavily on the assumption that you operate on a static data model, not one where you conjure up new tables based on metadata, so there is very little support for programmatically doing anything dynamic (simple or not), and nothing is easy. From C# your options are much better. If neither SMO nor DacFx take your fancy, you can also get the schema manually (`SqlDataReader.GetSchemaTable`) and generate the SQL based on that. – Jeroen Mostert Jun 17 '20 at 14:59
  • @JeroenMostert Thank you. That was very helpful. So, my conclusion is to come back and do it with query string and if I need the information of the table, use SqlDataReader.GetSchemaTable (like INFORMATION_SCHEMA in SQL). In C# I can also modify the last string from which the current table is made. – Iraj Jun 17 '20 at 15:24

1 Answers1

1

You are probably looking for this

In SQL Server, how do I generate a CREATE TABLE statement for a given table?

It generates statements for all tables, you need to add filter for a specific table you want to duplicate

Dmitriy Bayraktar
  • 126
  • 3
  • Thank you. But I think it's too complicated for my purpose. I will be better off doing it in C# as my final aim is to insert the SQL query in C#. – Iraj Jun 17 '20 at 15:27