Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Duplicate/copy tables from one DB to another
Message
De
21/09/2017 04:20:35
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
 
 
À
20/09/2017 21:43:01
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Maintenance bases de données
Divers
Thread ID:
01654465
Message ID:
01654478
Vues:
59
>>>Hi,
>>>
>>>Say I have two tables in a SQL Server database, Parent and Child.
>>>I want to copy/duplicate these two tables in another database keeping all records with their PK values the same. So that the second copy of the tables (in a new DB) is exactly the same as in the first.
>>>How do you do it?
>>
>>Connection having access to both databases you simply use select into:
>>
>>
>>select * into targetDb..targetTable from sourceDb..sourceTable
>>
>
>That will work the first time, but if the target table exists, it will crash.
>Offhand, don't know the the SQL syntax to test and delete if present, but that will make this OK.

He was asking for "copy" aka the target doesn't exist. If it exists, then simply change "select into from ..." to a "insert into ... select ..." query. To test the existence you can use "if exists (...)". i.e.:
if exists ( select  *
                from    sys.tables
                where   [name] = 'MyTarget'
                        and is_ms_shipped = 0 )
  begin 
      insert into myTarget 
      select * from mySource;
  end;  
else
  begin
     select * into myTarget from mySource; 
  end;
Or use DROP if you want to create afresh:
if exists ( select  *
                from    sys.tables
                where   [name] = 'MyTarget'
                        and is_ms_shipped = 0 )
  begin 
      drop table myTarget;
  end;  
select * into myTarget from mySource; 
Of course this is oversimplified version. You need to take care of primary key violations and such. IMHO you shouldn't even try this if you are not fluent in T-SQL and get help from DBA. There are also tools like RedGate's SQLCompare, that checks the data structure differences, SQL Data Compare, SQLBackup (SQL Toolbelt in short).
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform