In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more
#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable. You can only add a (non PK) index to a … Read more
You are just missing the words “primary key” as far as I can see to meet your specified objective. For your other columns it’s best to explicitly define whether they should be NULL or NOT NULL though so you are not relying on the ANSI_NULL_DFLT_ON setting. CREATE TABLE #tmp ( ID INT IDENTITY(1, 1) primary … Read more
No, a temp table is limited to the scope of your database connection. You can use the temp table in subsequent calls to the procedure during the same database connection, but other connections cannot access it. They can create a table by the same name, but each temp table will be independent. Temp tables go … Read more
Actually using a table VARIABLE, an in-memory table, is the optimal way to go. The #table creates a table in temp db, and ##table is global – both with disk hits. Consider the slow-down/hit experienced with the number of transactions. CREATE PROCEDURE [dbo].[GetAccounts] @AccountID BIGINT, @Result INT OUT, @ErrorMessage VARCHAR(255) OUT AS BEGIN SET NOCOUNT … Read more
Same thing, Just start the table name with # or ##: CREATE TABLE #TemporaryTable — Local temporary table – starts with single # ( Col1 int, Col2 varchar(10) …. ); CREATE TABLE ##GlobalTemporaryTable — Global temporary table – note it starts with ##. ( Col1 int, Col2 varchar(10) …. ); Temporary table names start with … Read more
CREATE PROCEDURE [MySPROC] AS BEGIN –supplying a data contract IF 1 = 2 BEGIN SELECT cast(null as bigint) as MyPrimaryKey, cast(null as int) as OtherColumn WHERE 1 = 2 END CREATE TABLE #tempSubset( [MyPrimaryKey] [bigint] NOT NULL, [OtherColumn] [int] NOT NULL) INSERT INTO #tempSubset (MyPrimaryKey, OtherColumn) SELECT SomePrimaryKey, SomeColumn FROM SomeHugeTable WHERE LimitingCondition = true … Read more
No, at the moment they don’t exist in PostgreSQL. If you truly need a memory table you can create a RAM disk, add a tablespace for it, and create tables on it. If you only need the temporary table that is visible between different sessions, you can use an UNLOGGED table. These are not true … Read more
Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously. A local temporary table can be used if it defined before calling sp_executesql e.g. CREATE TABLE #tempTable(id int); execute sp_executesql N’INSERT INTO #tempTable SELECT myId FROM … Read more
For the first case, no, it is not possible, because #temp is a local temporary table, and therefore not visible to other connections (it’s assumed that your users are using separate database connections). The temp table name is aliased to a random name that is generated and you reference that when you reference your local … Read more