How to copy data from one Azure SQL database to another via SQL Script

/ patrickschiefer

There are many scenarios were you want to copy data from one database to another, in my case it was a Business Central Upgrade which I’ve done some month ago. And I somehow lost some data on the way and had to transfer it again.

This was the first time I worked with Azure SQL so I didn’t know that there are some pitfalls. One of it is that the databases are isolated. So you cannot access two databases from the same connection (maybe there was also a misconfigurtion).

If you are having this issue there is simple solution. It is called “External Table” (see on Microsoft learn)

To create an external table you first need a data source (learn)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YOUR_PASSWORD';
CREATE DATABASE SCOPED CREDENTIAL ScopedDBCreds
WITH IDENTITY = 'UserName',
SECRET = 'YOUR_PASSWORD'


CREATE EXTERNAL DATA SOURCE SourceDatabase WITH
    (TYPE = RDBMS,
    LOCATION = 'NAME_OF_THE_AZURE_SQL_INSTANCE.database.windows.net',
    DATABASE_NAME = 'SourceDatabaseName',
    CREDENTIAL = ScopedDBCreds,
) ;

As you see we use the password and the user to store credentials to the source database

Afterwards you could create an external table,

[Cronus AG$Location.source] is the new aliasname from the table

[Cronus AG$Location] is the original name in the source database

CREATE EXTERNAL TABLE [dbo].[Cronus AG$Location.source]
WITH
( DATA_SOURCE = SourceDatabase)
AS
SELECT * FROM [dbo].[Cronus AG$Location]

Now you can use the external table like a normal table in the same database

INSERT INTO [dbo].[Cronus AG$Location] FROM [dbo].[Cronus AG$Location.source]

Create multiple external Tables in batch

Because I had to copy the complete data from an extension I had to create many external tables, so I wrote a script which is creating a creation script 


DECLARE c CURSOR  FOR select name from sys.tables where name like 'MyPrefix%'
OPEN c 
DECLARE @NAME VARCHAR(250)
FETCH NEXT FROM c INTO @NAME
DECLARE @first bit

WHILE @@FETCH_STATUS = 0 
BEGIN
	print 'CREATE EXTERNAL TABLE [dbo].['+@name+'.bak]'
	print '('
	DECLARE c2 CURSOR  FOR select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS col where TABLE_NAME = @NAME
	OPEN c2 
	DECLARE @col_name VARCHAR(250)
	DECLARE @type VARCHAR(250)
	DECLARE @len int
	FETCH NEXT FROM c2 INTO @col_name, @type, @len
	SET @first = 1
	while @@FETCH_STATUS = 0 
	begin
		if @first = 0
		begin
			print ','
		end

		if @type = 'varchar' or @type = 'nvarchar'
		begin
			print '['+@col_name+'] ['+@type+']('+CONVERT(VARCHAR(20), @len)+')'
		end
		else
		begin
			print '['+@col_name+'] ['+@type+']'
		end
		SET @first = 0
		FETCH NEXT FROM c2 INTO @col_name, @type, @len
	end
	CLOSE c2
	DEALLOCATE c2

	print ')'
	print 'WITH'
	print '( DATA_SOURCE = SourceDatabase '
	print ''
	print 'GO'
	print ''

	FETCH NEXT FROM c INTO @NAME
END

CLOSE c
DEALLOCATE c

So lets bring some light in this script

First I create a cursor which is looping over all tables starting with “MyPrefix” which is the prefix i

DECLARE c CURSOR FOR select name from sys.tables where name like 'MyPrefix%'
OPEN c
DECLARE @NAME VARCHAR(250)
FETCH NEXT FROM c INTO @NAME
DECLARE @first bit

WHILE @@FETCH_STATUS = 0 
BEGIN
	print 'CREATE EXTERNAL TABLE [dbo].['+@name+'.bak]'
	print '('

Afterwards I create a second cursor which is getting all columns from the current table. This was neccassary because there was a problem with datatypes

DECLARE c2 CURSOR  FOR select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS col where TABLE_NAME = @NAME
OPEN c2 
DECLARE @col_name VARCHAR(250)
DECLARE @type VARCHAR(250)
DECLARE @len int
FETCH NEXT FROM c2 INTO @col_name, @type, @len
SET @first = 1
while @@FETCH_STATUS = 0

Using this nformation, the rest of the script is just creating an output. If you save this output to a file you have valid create sql script.

Leave a Reply