Create Table in Local Database from Linked Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Create Table in Local Database from Linked Server

Author

Topic

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2014-01-10 : 05:40:12

HI,

I have linked my desired source server using a read only login, an I am in mine as SA. I can see all my desired tables listed. I can see four scripting options, but create table is not one of them.

I would like to create some of those tables on my local server.

Some I would just like to copy, as they are static tables. Others i could either copy, or just creating the table's structure would be ok.

Can this be done? If so how?

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2014-01-10 : 05:54:12

Additional info on the linked server(just incase changes anything)...

The linked server is a remote sql server with replicated tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 06:19:20

quote:
Originally posted by rocksteer

HI,

I have linked my desired source server using a read only login, an I am in mine as SA. I can see all my desired tables listed. I can see four scripting options, but create table is not one of them.

I would like to create some of those tables on my local server.

Some I would just like to copy, as they are static tables. Others i could either copy, or just creating the table's structure would be ok.

Can this be done? If so how?

yes
you can use query like below


SELECT * INTO NEwTable FROM LinkedServer.dbname.dbo.table

to create a table in your db corresponding to linked server table with data

if you need just structure and no data use like this


SELECT TOP 0 * INTO NEwTable FROM LinkedServer.dbname.dbo.table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2014-01-10 : 06:54:25

That was just what I needed.

Thanks much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:05:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources