@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects. To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used … Read more
This is a long standing parser issue. You need to get rid of the table prefix or wrap it in square brackets. i.e. delete from @CompanyGroupSites_Master where CompanyGroupID = @CompanyGroupID or delete from @CompanyGroupSites_Master where [@CompanyGroupSites_Master].CompanyGroupID = @CompanyGroupID Not delete from @CompanyGroupSites_Master where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID
Use a temporary table in PostgreSQL. For your example: CREATE TEMP TABLE product_totals ( product_id int , revenue money ); The manual about CREATE TABLE: If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction … Read more
If you’re using a table variable, you can’t do it. If it were a table, you could truncate it or use DBCC CHECKIDENT. But, if you have to use a table variable, you have to use something other than an identity column. Or, more accurately, use the identity column in your table variable but output … 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
A table alias cannot start with a @. So, give @Temp another alias (or leave out the two-part naming altogether): SELECT * FROM @TEMP t WHERE t.ID = 1; Also, a single equals sign is traditionally used in SQL for a comparison.
You can define a composite primary key like this: DECLARE @MyTable TABLE ( PersonID INT NOT NULL, Person2ID INT NOT NULL, Description NVARCHAR(100), PRIMARY KEY (PersonID, Person2ID) );
On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don’t need to update the values in the table itself. So from the code you posted you could use this approach for @TSku but not for @RelPro Example … Read more
Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET … Read more
Table variables are automatically local and automatically dropped — you don’t have to worry about it.