Why this SELECT INTO DID NOT WORK

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

General SQL Server Forums

New to SQL Server Programming

Why this SELECT INTO DID NOT WORK

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2008-11-06 : 15:07:42

Hi all.

Here is my problem.

IF OBJECT_ID('dbo.BasePairDistance') IS NOT NULL
DROP TABLE dbo.BasePairDistance
GO

CREATE TABLE crwdb.dbo.BasePairDistance
(SeqID int NOT NULL
,Position1 int NOT NULL
,Base1 char(1) NOT NULL
,Position2 int NOT NULL
,Base2 char(1) NOT NULL
,HbondDistance float
,CentralDistance float
,C1Distance float)

SELECT SSM.SeqID AS SeqID
,SSM.nucleotideID AS Position1
,SSM.base AS Base1
,SSM.PairingID AS Position2
,S.Base AS Base2
,dbo.fn_HbondDistance(SSM.nucleotideID,SSM.PairingID) AS HbondDistance
,dbo.fn_CentralDistance(SSM.nucleotideID,SSM.PairingID) AS CentralDistance
,dbo.fn_C1Distance(SSM.nucleotideID,SSM.PairingID) AS C1Distance
INTO crwdb.dbo.BasePairDistance
FROM ....
WHERE ...

Without that "INTO", everything works fine. But it fails when I add that "INTO".
Could anyone tell me why?

Thanks a lot

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-06 : 15:13:29

INSERT INTO crwdb.dbo.BasePairDistance

SELECT SSM.SeqID AS SeqID
,SSM.nucleotideID AS Position1
,SSM.base AS Base1
,SSM.PairingID AS Position2
,S.Base AS Base2
,dbo.fn_HbondDistance(SSM.nucleotideID,SSM.PairingID) AS HbondDistance
,dbo.fn_CentralDistance(SSM.nucleotideID,SSM.PairingID) AS CentralDistance
,dbo.fn_C1Distance(SSM.nucleotideID,SSM.PairingID) AS C1Distance
INTO crwdb.dbo.BasePairDistance
FROM ....
WHERE ...

Select ... into actually creates the table, so it was trying to create a table that already exist, hence the error.

jim

mavershang
Posting Yak Master

111 Posts

Posted - 2008-11-06 : 15:40:05

Thanks. So does that mean if I use "insert into", it will work?

quote:
Originally posted by jimf

INSERT INTO crwdb.dbo.BasePairDistance

SELECT SSM.SeqID AS SeqID
,SSM.nucleotideID AS Position1
,SSM.base AS Base1
,SSM.PairingID AS Position2
,S.Base AS Base2
,dbo.fn_HbondDistance(SSM.nucleotideID,SSM.PairingID) AS HbondDistance
,dbo.fn_CentralDistance(SSM.nucleotideID,SSM.PairingID) AS CentralDistance
,dbo.fn_C1Distance(SSM.nucleotideID,SSM.PairingID) AS C1Distance
INTO crwdb.dbo.BasePairDistance
FROM ....
WHERE ...

Select ... into actually creates the table, so it was trying to create a table that already exist, hence the error.

jim

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 15:46:29

DEPENDS. If your select statement matches to the structure then it will insert otherwise, you'll have to modify your table structure to fit it.
For example, your BASE1 and BASE2 are char(1) if any of them returns 2 or more characters (trailing spaces most of the time) then it will fail.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-06 : 16:00:49

Easier to just use the insert into statement in my opinion, doing a select into... statement is best for a quick table creation using the same data from another

mavershang
Posting Yak Master

111 Posts

Posted - 2008-11-06 : 16:53:52

Thanks a lot, guys.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-07 : 05:07:14

Also SELECT INTO is not a set operation -- it is a *lot* slower than and INSERT INTO

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources