SQL Server: how to insert to temporary table?

Question

I have one Temporary Table

CREATE TABLE #TEMP (TEMP_ID INT IDENTITY(1,1))

And I would like to insert records to that table, How can I?I do as follow:

INSERT INTO #TEMP DEFAULT VALUES

But sometimes it doesn't work. What it might be?And I would like to know lifetime of temptable in SQL Server. Please Help me!

Thanks all!

Answer 1

Works for me!

CREATE TABLE #TEMP (TEMP_ID INT IDENTITY(1,1))

--And I would like to insert records to that table, How can I?I do as follow:

INSERT INTO #TEMP DEFAULT VALUES
INSERT INTO #TEMP DEFAULT VALUES
INSERT INTO #TEMP DEFAULT VALUES
INSERT INTO #TEMP DEFAULT VALUES

select * from #TEMP

Gives:

TEMP_ID 
1
2
3
4

Keep in mind it needs to be the same "batch" or single query etc.

PK :-)

Answer 2

That looks fine. Also INSERT INTO #TEMP (TEMP_ID) VALUES (DEFAULT). When you say that sometimes it doesn't work, what error are you getting? # tables only have a lifetime and scope of your session.

Answer 3

Not sure what you mean about "sometimes it doesn't work."

However, a local temp table (a single #) lifetime is the current session or scope (such as the stored proc or function duration). CREATE TABLE on MSDN as a lot more with examples in the section "Temporary Tables"