Create table from table on a Linked Server

Posted on 2008-11-03
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
    LVL 13

    Accepted Solution

    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

    LVL 3

    Author Comment

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

    Expert Comment

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now