Transfering data from one sql database to other in azure using external table

Question

I want to transfer data from one database to other in SQL 2017 Azure. I am following this way: both database have same schema but need to transfer only one tables data from 1 db to other.....

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'e4a421f5-8f73-4c68-8a4c-f5c9dc429dde';

CREATE DATABASE SCOPED CREDENTIAL dbCredential WITH IDENTITY = 'mlsqladmin', SECRET = 'e4a421f5-8f73-4c68-8a4c-f5c9dc429dde';

CREATE EXTERNAL DATA SOURCE restoredDatabase WITH

(

    TYPE=RDBMS,

    LOCATION='sqlazewdmlitbunkerpit001.database.windows.net',

    DATABASE_NAME='sqdazewdmlitbunkerpit001_back',

    CREDENTIAL= dbCredential

);   


CREATE EXTERNAL TABLE [Admin].[Estimates2016] (

    [CommandId] int  NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Active] [int] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [UpdatedBy] [int] NULL,
    [CreatedTimestamp] [datetime] NOT NULL,
    [UpdatedTimestamp] [datetime] NULL

)

WITH (

     DATA_SOURCE = restoredDatabase,
      SCHEMA_NAME = 'dbo', -- This is the name of the schema on the host database
   OBJECT_NAME = 'Transactions' -- Name of the table on the host database
);

--Here transactions is a view in other database with the schema as mentioned in --Extimates 2016
like --create view transactions select * from Admin.Command -- Here everything is working fine except the last insert statement which is below and the error is --Msg 116, Level 16, State 1, Line 45 --Only one expression can be specified in the select list when the subquery is ---not --introduced with EXISTS. --Msg 213, Level 16, State 1, Line 45 --Column name or number of supplied values does not match table definition.

SET IDENTITY_INSERT  Command ON   
INSERT INTO Admin.Command  values  ((select  * from Admin.ESTIMATES2016 )  ) 
SET IDENTITY_INSERT  Admin.Command OFF


SELECT * FROM ESTIMATES2016 WHERE YEAR = 2016;

--obviously we need to drop all those after data transfer is done.... DROP EXTERNAL TABLE Admin.Estimates2016; DROP EXTERNAL DATA SOURCE restoredDatabase; DROP DATABASE SCOPED CREDENTIAL dbCredential; DROP MASTER KEY; — Venugopal Appayanna

Venugopal Appayanna Venugopal Appayanna · Accepted Answer · 2018-08-10T20:02:15

INSERT INTO Admin.Command values ((select * from Admin.ESTIMATES2016 ) )

remove values and brackets include the insert parameters

This worked for me might be simple but took more time to find

INSERT INTO Admin.Command(CommandId,Name,Active,CreatedBy,UpdatedBy,CreatedTimestamp,UpdatedTimestamp) select * from Admin.Estimates2016 where CommandId not in (select CommandId from Admin.Command)

Transfering data from one sql database to other in azure using external table

1 Answers