[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Create table from table on a Linked Server

Posted on 2008-11-03
Medium Priority
Last Modified: 2012-05-05
Using MS SQL Server 2000 I'm trying to create a new table that will contain all table and fields from another table on a Linked Server.  

CREATE TABLE INT_USER AS(select * from interaction.interaction.dbo.INT_USER);

I get the following error message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I can just run the select statement and return records.

Thanks for your help.
Question by:eshurak
  • 2
LVL 13

Accepted Solution

St3veMax earned 2000 total points
ID: 22868913
Does the table already exist? If not you can use the code below.


SELECT * INTO NewDatabase.dbo.INT_User FROM LinkedServer.OldDatabase.dbo.INT_User

Open in new window


Author Comment

ID: 22868955
Great that worked.  Could you tell me why? Or when to use create table vs. select * into?
LVL 13

Expert Comment

ID: 22868977
Quite simply; If you have a table that you wish to insert into that already exists - use Insert Into otherwise use Select Into.

Be aware that depending on the number of rows in your source database; this could take some time. Also be aware of referential integrity on your new database.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question