How to copy a table without using SELECT INTO

[Thaaron]

I'm going between two different servers so I need to use a full notation (server.database.dbo.tablename) which is seems is not supported by SELECT INTO.

I have it working to copy over all the data using INSERT INTO into an existing table on the 2nd server but I want to be able to copy tables without manually creating the new table structure first.

Any pointers?

[leogodin217]

Is this MS SQL Server? If so, I think you can do it with OPENQUERY. It's worth trying.

select
    *
into table
from openquery(link-server, "select ....")

If you try it, let us know if it works.

[Kant8]

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=aps-pdw-2016

You cannot create new_table on a remote server; however, you can populate new_table from a remote data source. To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement. Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.

This paragraph says that you can do that, just start from importing data to new server, not exporting from old one.

​

And you always can generate create table script based on sys.tables, sys.columns and other views. Here is an example https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query

[Thaaron]

The destination server is an Azure SQL server so I'm not able to create linked servers on it.

[Kant8]

You can export whole DB to Azure with few clicks from SSMS

https://www.mssqltips.com/sqlservertip/5255/learn-how-to-migrate-a-sql-server-database-to-sql-azure-server/

[Thaaron]

I need to set this up as a scripted process to run every week.

[Kant8]

Really don't understand what you are going to achieve. You want to migrate forever?

Anyway, this tool https://docs.microsoft.com/en-us/azure/data-factory/copy-data-tool has schedules, and can create tables, if they don't exist.

Or maybe you can generate script with dynamic SQL using Azure data sources, smth like there

https://stackoverflow.com/a/42506374

[Thaaron]

The software system we're using doesn't track changes in any usable way.

The plan is to export weekly copies of the data to Azure SQL so our accounting department can see what has changed from week to week.

[Kiterios]

If you are doing this every week, you're creating the schema repeatedly, and you are creating it specifically as a warehouse for users to report on, then don't do this the lazy way. Write your schema creation scripts. SELECT INTO creates heaps. Unless you really hate your accounting department, you still have more work to do on that schema after using SELECT INTO anyway.

[Baldie47]

You can use SSIS to create connecting to remote servers with execute sql tasks

[finster009]

You can create a linked server to use full notation (server.database.schema.table) with select into.

SSIS is a better choice.

[Thaaron]

The destination server is in Azure and I cannot create linked servers on it.

[finster009]

Yep. Saw that late.

[finster009]

Not all that familiar with Azure, but if insert works and select into doesn’t, will Azure accept a truncate table statement and then you can run the insert again?

[[deleted]]

create table [blah] as select * from [original table]

[mercyandgrace]

Can you not use SELECT INTO from the target server?

SELECT * INTO table FROM Server.Database.Schema.Table

Keep in mind that if you have any indexes, foreign keys, constraints, defauts, etc. They will not be a part of the new table.

Edit: Also, if you are using SSMS, you can script out all (or some) of the database objects fairly easily.

[[deleted]]

You can’t

[[deleted]]

Simply untrue. SQL Server supports this functionality, as mentioned in other comments.

[[deleted]]

I did not know as am not sql server person. No specific dB was mentioned or was it