Geek Answers Handbook

Select to statement where the source is another database

How to copy data from one database to another database with the same table structure and save key identifiers?

I use Sql Server 2012 "Denali" and I want to copy some data from the Sql Server 2008 database. The tables are exactly the same, but I want the data from the old database to be added to the new Denali database. Databases are located on different servers.

So I want something like

USE newDB; GO SELECT * INTO newTable FROM OldDb.oldTable WITH (KEEPIDENTITY); GO

Anyone have a suggestion to make this workable?

+4
source share
6 answers

Configure the linked server and specify it in your request. You may need to use IDENTITY_INSERT .

SSIS components embedded in SSMS can also load data from various sources (XML, flat file, or local / remote server).

+2
source

This problem today and it did not work :( You must use fully qualified database names if both databases are on the same server. Do not forget the .dbo scheme .

 Select * INTO [NEW DB].dbo.Lab_Tests from [OLD DB].dbo.Lab_Tests
+2
source

If your database is on the same server, then you can do it as follows:

 insert into newTable select col1,col2 from OldDB.dbo.OldTable
+1
source

If you want to insert explicit values ​​in the IDENTITY field, you can use SET IDENTITY_INSERT table OFF :

 CREATE TABLE dbo.Destination ( Id INT IDENTITY(1,1) PRIMARY KEY ,Name NVARCHAR(100) NOT NULL ); INSERT dbo.Destination VALUES ('A'), ('B'), ('C'); GO SET IDENTITY_INSERT dbo.Destination ON; INSERT dbo.Destination(Id, Name) SELECT T.Id, T.Name FROM (VALUES (10,'D'), (11,'E')) AS T(Id, Name); --or SourceDB.Schema.SourceTable SET IDENTITY_INSERT dbo.Destination OFF; GO INSERT dbo.Destination VALUES ('????????'); GO SELECT * FROM dbo.Destination DROP TABLE Destination;
0
source

There are certain restrictions when copying a database from one server to another (remotely).

So, you need to follow the steps to completely transfer the database from one server to another with the same identifiers and restrictions.

  • In short, generate a Script database, i.e. right-click on the database> Tasks> Generate scripts> Select database> Mark these truths: Triggers, indexes, primary and foreign keys, and others, if any. > Select "Object types": check all "true", except for "User" (later you can create users on the new server manually). > Select all tables, SP and other objects> Script in a new window or files (at your discretion)> "Finish".

  • Create a Script to create the database and run it on the server.

  • Run step 1. Script on the new server, make sure that the new database is selected in the query window.

  • Disable all restrictions on the new server> EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

  • I think using the wizard is fast, so use the export database wizard from the old database server to transfer data from the old database server to the new database server.

  • Re-enable all restrictions on the new server> EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

PS: the version does not matter, since you must migrate the database supposedly in the current version in your case of SQL Server 2008, and after copying the database you can change the version of the database.

Database Properties> Select a page: Options> Compatibility Level> select the drop-down version.

0
source

Suppose you are in an old database instance.
What you can do is:

 Select * Into denali.dbo.newTable FROM oldTable

It seems to copy oldTable (structure and content) to newTable and export it to another database.

0
source

More articles:


All Articles