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?

Are the tables on the same SqlServer? – Keith Bloom Feb 08 '10 at 14:50 — Keith Bloom, Feb 08 '10 at 14:50

score 3 · Accepted Answer · answered Feb 08 '10 at 14:55

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.

score 1 · Answer 2 · answered Feb 08 '10 at 14:51

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]

answered Feb 08 '10 at 14:51

Tim Lentine

score 0 · Answer 3 · answered Feb 08 '10 at 15:24

0

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

answered Feb 08 '10 at 15:24

Lill Lansey

3 Answers3