bcp is probably a better way to achieve this and works well with Azure SQL Database. It's a command-line tool that enables you to extract and load data easily. For example:
bcp "dbo.yourTable" out c:\temp\temp.bcp -S "yourSourceServer" -d "yourSourceDatabase" -T -n
bcp "dbo.yourTable" in c:\temp\temp.bcp -S "yourTargeteServer" -d "yourTargetDatabase" -U someSQLLogin -P somePassword -E -n
-E switch in the
bcp in statement to keep your
IDENTITY values. Replace the table, server and database names in my template above and see if you can get the first statement to run. Note in the first example I am using Windows Authentication (
-T) but SQL Authentication in the second one (
-U for User and
-P for password). The main MSDN page for bcp provides some good examples:
If your table already has some data in it, consider inserting your data into a staging table first, then
INSERT to your main table, eg
SET IDENTITY_INSERT yourTargetTable ON
INSERT INTO yourTargetTable ( columnList )
FROM yourStagingTable s
WHERE NOT EXISTS
FROM yourTargetTable t
WHERE s.yourPrimaryKey = t.yourPrimaryKey
SET IDENTITY_INSERT yourTargetTable OFF
There are other methods of achieving this.
- For example, you could accomplish the same thing using SQL Server Integration Services (SSIS) but there is some overhead to creating an SSIS project.
- For example, if your table is not too big, create a copy of your table in tempdb using something like
SELECT [all columns except the identity column] INTO tempdb.dbo.scratchTable FROM yourMainTable. Run the
Generate Scripts option against this copy then delete your scratch table.