Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
My Stored Proc is Replicated to other DB Server, When iam trying to ALTER my modified SP in Publisher DB, ALTER statement not working, even it's not throwing any errors also.
My SP has very simple logic [Just for get data from DB] , no of lines 680, no of chars 32000 [Approx].
If i decrease the length of SP, it's working fine.
Somehow I got the value -1.#IND into several records in a table in SQL 2000 DB.
I assume that it is a kind of €œNot A Number€??
The problem I am facing now, is that I can not modify the value to a legal value. Nor can I delete the record that holds the number. I get the error message:
€œAnother user has modified the contents of this table or view, the database row you are modifying no longer exists in the database. Database error: €˜[Microsoft][ODBS SQL Server Driver][SQL Server] A floating point exception occurred in the user process. Current transaction is cancelled.€?
I'm attempting to move my database from one server to the other using attach/detach method. I'm able to reattach everything just fine on the new server but I get the following error when trying to receive messages.
"Service Broker login attempt failed with error. Connection handshake failed. Error 15581 occurred while initializing the private key corresponding to the certificate....." " Please create a master key in the database or open the master key in the session...."
I have a master key in the master database so I attempted the following:
open master key decryption by password = 'pwd'
alter master key add encryption by service master key
and I receive the following error on the alter master key command:
"An error occurred during decryption"
Any suggestions on how to recover the database to use the same database key? Do I need to regenerate the key?
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Is it possible to insert data into a table from a temporary table that is inner join? Can anyone share an example of a stored procedure that can do this? Thanks, xyz789
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference. pls reply. first: create table ##temp ( name varchar(25), roll int ) insert into ##temp values('banti',1) select * from ##temp second: create table #temp ( name varchar(25), roll int ) insert into #temp values('banti',1) select * from #temp
both works fine , then what is the difference waiting for ur reply Banti"
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
I am using SQL 2000 I have the following code. When saving it, I am getting an error: There is already an object named '##tbl' in the database
This is although #tbl is dropped. Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int set @x=1 IF @x=0 begin create table #tbl ( abc int ) drop table #tbl end
IF @x=1 begin create table #tbl ( abc int ) drop table #tbl end
I have to create a temporary table for generating a report in VB. Pls help how to check the temporary table name in database. I want if exits than drop and create a new one.
The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date ========================================================= Sales | 20,000.00 | 10,000.00 | 30,000.00 Total Sales | 20,000.00 | 10,000.00 | 30,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
Declare @Temp Table(id_customer int, id_product varchar(10)) Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103') Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102') Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp Declare @results VarChar(100) Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '') From @Temp where id_customer = 12 Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
Hi All, DECLARE @MyTableVar table( EmpID nvarchar(10) ) select login_id from cpm into @MyTableVar Above syntax is not working.... Actually i want to table variable and store the result returned by stored procedure. How to do that... Thanks and reagards A
I'm trying to drop a temporary table. I keep getting this error: Cannot drop the table '#temp_table', because it does not exist in the system catalog. I tried the following but it did not work, so help would be appreciated. if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
Hello once again, My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed. I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code. What are my options? Any help would be greatly appreciated. Pettrer
Im trying to find a table and Drop in T-SQL using this script.
/* Start */ Use Students IF exists (Select * from information_schema.tables where table_name ='##Exams_result) drop table ##Exams_result go Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way... Any sugestion?
Luiz Lucasi lc@culting.com Rio de Janeiro - Brazil
When we are writing a big query that against to 25+ tables (it has lots of lookup and lots of business logic in it so it will have lots of left outer joins, right outer joins, inner join, UNION, group by, and lots of SQL server functions) should we put everything in one select statement? Or we should create lots of temporary tables and then join them together?
My friend told me that I should use one statement instead of lots of temp tables in order to reduce the physical read. He does not like the temp table idea since there will be lots of user running the query in the same time and it will use lots of space. But I don’t believe that is true. (I believe when you put everything in one statement, SQL server still needs to create the result set in the temp db.) There will be lots of join going on and it could hurt the performance.
I believe that 1.We should create the temp tables and then join the temp tables together later to get the final results. we can create the index on the temp table at runtime. 2.If the order of columns in the index does not match to our where clause, then the index is useless. 3.If there is a SQL function in the where clause, the index is useless. (for example, where Datepart(yy, orderdate)=’1998’) 4.If there is a NOT in the where clause, the index is useless.
What do you think? Are you agree with me?
Following is the query. Is there any better way to do this?
Select d.Description as Division, c.Description as Region, b.StateCode as State, b.FacilityNumber, b.Name as FacilityName, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate,101) Else 'Pending' End) as PriorStdSurveyExit, (Case When a.ExitDate Is Not Null And a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+89,101) Else '' End) as ThreeMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+179,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+179,101) Else 'Pending' End) as SixMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+450,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+450,101) Else 'Pending' End) as FifteenMonthWindow From ( Select a.FacilityNumber, a.ExitDate, IsNull(b.FacilityNumber,0) as IJValid From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber ) a Left Join ( Select Distinct b.FacilityNumber From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join ComplianceRatingCode e on (a.ComplianceRatingCodeID = e.ComplianceRatingCodeID) Where a.VerbalResultDate is Not Null And (c.Description Like 'Standard%' And d.Description Like 'LTC%') And e.Description Like 'Category 4%' And a.ExitDate <= @InDate Union Select Distinct b.FacilityNumber From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join Citation f on (e.CitationID = f.CitationID) Inner Join SSLetterCode g on (f.SSLetterCodeID = g.SSLetterCodeID) Where a.VerbalResultDate is Not Null And d.Description Like 'LTC%' And a.ExitDate <= @InDate And g.SSLetterCode in ('J','K','L') Group By b.FacilityNumber ) a Right Join ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber
) b on (a.FacilityNumber = b.FacilityNumber) Where a.ExitDate >= b.ExitDate
) b on (a.FacilityNumber = b.FacilityNumber) ) a Right Join Facility b on (a.FacilityNumber = b.FacilityNumber) Left Join Region c on (b.RegionID = c.RegionID) Left Join Division d on (c.DivisionID = d.DivisionID) Order By d.Description, c.Description, b.FacilityNumber
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208" and show Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
We are having some issues with temporary tables (with # prefixes) within Stored Procedures.
When running a profile trace on them, the stored proc quite happily creates the temp # table (in fact several of them) but whenever it hits the first statement inserting data into one of them (and it doesn't matter which one), there is a 5-6 second delay.
By commenting out one and moving to the next piece of code, the same thing happens.
Following which, the rest of the Stored Prco runs fine and subsequent inserts into the # temp tables also run efficiently.
Is the stored proc getting recompiled perhaps ?
Any advice woul;d be appreciated.
we are running SQL Server 7.0, dont know whether that helps ?
I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?
--Query 1
SELECT * INTO #MOVEMENTS FROM [GW_DW].[dbo].[DimStatusHistory] d WHERE TransferFromToProgram<>'' AND d.Status=12 ;
