CODEWITHSUNDEEP
sql-serverinserttemp-tables
RedsDevils
RedsDevils

Reputation: 1433

SQL Server: how to insert to temporary table?

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!

Upvotes: 0

Views: 2530

Answers (3)

gbn
gbn

Reputation: 432200

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"

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89651

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.

Upvotes: 1

Paul Kohler
Paul Kohler

Reputation: 2714

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 :-)

Upvotes: 2

Related Questions