SELECT INTO confusion - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT INTO confusion

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2009-07-23 : 10:13:17

There has to be something I don't understand about tables created using SELECT INTO. I'm seeing something I can't explain, maybe someone can point out my error:


SELECT s.Value
INTO tblTarget
FROM tblSource s;

ALTER TABLE tblTarget
ADD Rec_Num int IDENTITY;

SELECT * FROM tblTarget; 
--this returns Value & Rec_Num, no problem
CREATE UNIQUE INDEX IX_tblTarget_Rec_Num ON tblTarget(Rec_Num);
--This works fine as well

--However...
SELECT * FROM tblTarget ORDER BY Rec_Num;
SELECT Rec_Num FROM tblTarget;
--These both return an error, Invalid column name 'Rec_Num'.

How is it that I can select * from the table and get Rec_Num back, and I can even create an index on Rec_Num, but I can't specify it in my SELECT statement?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-23 : 10:21:07

It worked for me right


declare @t table (InstId int, Date datetime, Readings int)
set dateformat dmy
insert @t
select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8


SELECT Readings
INTO tblTarget
FROM @t s;

ALTER TABLE tblTarget
ADD Rec_Num int IDENTITY;

SELECT * FROM tblTarget; 
--this returns Value & Rec_Num, no problem
CREATE UNIQUE INDEX IX_tblTarget_Rec_Num ON tblTarget(Rec_Num);
--This works fine as well

--However...
SELECT * FROM tblTarget ORDER BY Rec_Num;
SELECT Rec_Num FROM tblTarget

drop table tblTarget

ron2112
Starting Member

44 Posts

Posted - 2009-07-23 : 10:24:01

That's strange because it very definitely does not work here. I get the same error running your script. I'm running 2005 if that matters.

UPDATE: I ran your script against a 2008 db and it works. So the problem is 2005-specific.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-23 : 10:37:29

I guess so.I also ran the same against 2008.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 10:44:35

works fine for me in all versions.

try this:


SELECT s.Value
INTO tblTarget
FROM tblSource s;
GO
ALTER TABLE tblTarget
ADD Rec_Num int IDENTITY;
GO
CREATE UNIQUE INDEX IX_tblTarget_Rec_Num ON tblTarget(Rec_Num);
GO
SELECT * FROM tblTarget ORDER BY Rec_Num;
GO
SELECT Rec_Num FROM tblTarget;
GO

ron2112
Starting Member

44 Posts

Posted - 2009-07-23 : 10:45:01

At least I know it's not my own stupidity. I've found another way around it. Thanks!!!

ron2112
Starting Member

44 Posts

Posted - 2009-07-23 : 10:46:35

Russell, I suspect the GOs are forcing something to settle in and be recognized by the system. I'm doing this in an SP so I can't use the GOs unfortunately. But if you find a way to make it work without those, I'd love to take a look!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 10:55:46

[code]
CREATE PROC usp_test
AS
SET NOCOUNT ON

Create Table tblTarget (
[rec_num] int identity(1, 1) not null primary key,
[value] int
);

INSERT tblTarget ([value])
SELECT [value] FROM tblSource;

SELECT * FROM tblTarget ORDER BY Rec_Num;
GO[/code]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 10:57:01

notice that the PK is a unique index

ron2112
Starting Member

44 Posts

Posted - 2009-07-23 : 10:59:20

quote:

Originally posted by russell


CREATE PROC usp_test
AS
SET NOCOUNT ON

Create Table tblTarget (
	[rec_num] int identity(1, 1) not null primary key,
	[value] int
);

INSERT	tblTarget ([value])
SELECT [value] FROM tblSource;

SELECT * FROM tblTarget ORDER BY Rec_Num;
GO

That's actually the same work-around I found, to forgo the SELECT INTO and its performance benefits and just bite the bullet with a temp table. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources