Select * into SqlserverTable2 from SqlExpressTable1, can this be done

Question

Can I Select * into a Sqlserver Table from a SqlExpress Table the way I can from a Sqlserver Table to a Sqlserver Table using

Select * into  Table2 from  Table1

If so, what is the syntax?

Answer 1

Most likely you will first have to link the two SQL Server instances together first. The form you'd use after that would be:

SELECT * INTO TABLE2 FROM [ServerInstanceName].[DatabaseName].[SchemaName].[TableName]

Answer 2

These are distinct servers, so you would need to introduce a linked server, on the server where you intend to make a reference to the other server.

  sp_addlinkedserver @server= 'some_ip_or_URI',  @srvproduct= 'SQL Server'

Then you need to use the full name of the table/object needed in the query

  [Server Name].[Database Name].[Owner/Schema Name].[Object Name]

as in

 [test.private.mydomain.com].ClientDB.dbo.tblInvoices

Note the square brackets which are necessary of course, when the instance/server name include dots, spaces and such characters.

Answer 3

I ended up using ssms database import. Couldn't get the naming to work using select.