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

x
?
Solved

Create table from table on a Linked Server

Posted on 2008-11-03
3
Medium Priority
?
767 Views
Last Modified: 2012-05-05
Hello,
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.  

Using:
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.
0
Comment
Question by:eshurak
  • 2
3 Comments
 
LVL 13

Accepted Solution

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

HTH

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

Open in new window

0
 
LVL 3

Author Comment

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

Expert Comment

by:St3veMax
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.

HTH
0

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