Clone a Table RRS feed

  • Question

  • Hi There,

    I am trying to clone Table A, which warehouses every evening from a different Server.

    I cant do right click and Script table as> Create to as that will give me nulls.

    I cant do Select * INTO FROM as that would give me a static table 

    But instead i want a duplicate table that is as updated from Table A.


    • Edited by SQLC Tuesday, January 28, 2020 4:26 PM
    Tuesday, January 28, 2020 4:21 PM

All replies

  • use transactional or snapshot replication for this.

    Here is a tutorial.


    Tuesday, January 28, 2020 5:10 PM
  • Thank you  for the response.

    Replication components are not installed on the server.

    Also, this is a within a server. Do you have any other method to clone a table?


    Tuesday, January 28, 2020 8:56 PM
  • Perhaps a view would be what you are looking for. Perhaps even an indexed view.

    This view could be exposed the table on the other server as a linked server, ie

    Create View MyView
    select * from RemoteServerName.DatabaseName.SchemaName.TableName

    Tuesday, January 28, 2020 9:18 PM
  • Hi SQLC,

    >> I cant do right click and Script table as> Create to as that will give me nulls.

    If you want to duplicate the table with all its constraints & keys, using Script table as in SSMS is the best choice.  Right click the table ->Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window. But the new table has no rows.  You need to using below T-SQL to copy data from original table to new table.

    set identity_insert new table on
    insert into new table (column1, column2) select column1, column2 from original table
    set identity_insert new table off

    Please refer to the similar thread to get more information.

    Best regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 29, 2020 7:23 AM