insert tmpTable vs Table

Is there a difference between inserting data into a temp table vs a real
table? I'm using an SP and I created a temp table. Then tried to INSERT and
failed. Then, for troubleshooting, I created a real table and INSERT works
fine. What characteristic about temp tables am I missing?
Both tables exist and I do a SELECT * on both. But only the real table has
records.
thanks
CREATE PROCEDURE stp_DOD_TrackNumbers
@.PID int, @.SK int, @.IDD int
AS
--if exists (select * from dbo.sysobjects where id =
object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [#tmpDODSongs]
--CREATE TABLE [#tmpDODSongs] (
-- [ProjectID] [numeric](18, 0) NOT NULL ,
-- [SortKey] [numeric](18, 0) NULL ,
-- [OldIDD] [numeric](18, 0) NULL ,
-- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
--) ON [PRIMARY]
INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
VALUES (@.PID, @.SK, @.IDD)
--INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
--VALUES (@.PID, @.SK, @.IDD)
-- perform other tasks here before dropping temp table
--drop table [#tmpDODSongs]
GO> Then tried to INSERT and failed.
Could you be a bit more specific?|||I created a temp table #tmpDODSongs and logical table tmpDODSongs.
Both with the same attributes, structure, etc.
Using the same 17 records...
I could INSERT INTO the logical table tmpDODSongs with no problem.
When I tried to INSERT INTO temp table #tmpDODSongs - no records were
inserted.
I did not get any errors, just no inserted recods.
I used the same SP and would comment out one INSERT statement of the other.
I was just wondering if there was a consideration I needed to make for temp
tables.
thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23nGpR766FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Could you be a bit more specific?
>|||Where in the process is the select from the table?
shank wrote:
> Is there a difference between inserting data into a temp table vs a real
> table? I'm using an SP and I created a temp table. Then tried to INSERT an
d
> failed. Then, for troubleshooting, I created a real table and INSERT works
> fine. What characteristic about temp tables am I missing?
> Both tables exist and I do a SELECT * on both. But only the real table has
> records.
> thanks
> CREATE PROCEDURE stp_DOD_TrackNumbers
> @.PID int, @.SK int, @.IDD int
> AS
> --if exists (select * from dbo.sysobjects where id =
> object_id(N'[#tmpDODSongs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> --drop table [#tmpDODSongs]
> --CREATE TABLE [#tmpDODSongs] (
> -- [ProjectID] [numeric](18, 0) NOT NULL ,
> -- [SortKey] [numeric](18, 0) NULL ,
> -- [OldIDD] [numeric](18, 0) NULL ,
> -- [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
> --) ON [PRIMARY]
>
> INSERT INTO #tmpDODSongs (ProjectID, SortKey, OldIDD)
> VALUES (@.PID, @.SK, @.IDD)
> --INSERT INTO tmpDODSongs (ProjectID, SortKey, OldIDD)
> --VALUES (@.PID, @.SK, @.IDD)
>
> -- perform other tasks here before dropping temp table
> --drop table [#tmpDODSongs]
> GO
>|||I'm using QA to select the tracks from each table for the sake of
troubleshooting.
SELECT *
FROM #tmpDODSongs
SELECT *
FROM tmpDODSongs
thanks
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e25lJP76FHA.636@.TK2MSFTNGP10.phx.gbl...
> Where in the process is the select from the table?
> shank wrote:|||"shank" <shank@.tampabay.rr.com> wrote in
news:#nx8da76FHA.3752@.tk2msftngp13.phx.gbl:
> I'm using QA to select the tracks from each table for the sake of
> troubleshooting.
> SELECT *
> FROM #tmpDODSongs
[snip]
[snip]
Well, as per the code above, you drop the temp table - so if you try to
select out of it afterwards, you wouldn't get any data - would you?
Niels