select into in error

Question

Post reply

select into in error

ryan_dansie

Grasshopper

Points: 12
More actions
May 19, 2008 at 5:10 am

#187764

Hi i am new to this forum and to sql so i would be grateful for any help which can be given here. Im having trouble copying a table into another database. This is the query:
use test
select testTable1.* into testTable1 in 'test2.mdb' from testTable1
go
and the error i get is this:
Incorrect syntax near the keyword 'in'.
Could anyone tell me what ive done wrong. Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Assuming you have both databases test and test2 running on the same server:

[font="Courier New"]use test

select testTable1.*

into test2.dbo.testTable1

from testTable1

go[/font]

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

I would like a little more info first.

Can you tell me what the different elements are? What's mdb, and what are the exact DBs and tables and schemas involved?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

ryan_dansie Grasshopper Points: 12 More actions · Answer 3

Michael Earl (5/19/2008)
Assuming you have both databases test and test2 running on the same server:
[font="Courier New"]use test
select testTable1.*
into test2.dbo.testTable1
from testTable1
go[/font]

cheers that did the job okay what about if they are on different servers

Michael David Right there with Babe Points: 788 More actions · Answer 4

use test

select Server.DB.dbo.testTable1.*

into Server2.test2.dbo.testTable1

from testTable1

go

The Server2 must be alinked server.

BTW, Dont do that. I am talking about select into. Bad SQL practice.

Use

INSERT INTO Table1(Col1,col2...)

SELECT col1,Col2 FROM Table2

If it is a temp table, create it first with a create table statement..

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Michael David (5/19/2008)
use test
select Server.DB.dbo.testTable1.*
into Server2.test2.dbo.testTable1
from testTable1
go
The Server2 must be alinked server.
BTW, Dont do that. I am talking about select into. Bad SQL practice.
Use
INSERT INTO Table1(Col1,col2...)
SELECT col1,Col2 FROM Table2
If it is a temp table, create it first with a create table statement..

Why exactly is select into a bad practice? It's actually FASTER than INSERT INTO. As to the issue that was initially reported form 7.0 about perhaps locking some system tables, any lock that might occur is so transient that it's not likely to cause an issue.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?