I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
View 9 RepliesI could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
domainName=CONCAT('domain',User Name);
Hi All,
"Happy New Year!"
I need to append data to the existing Table1 from a .txt file stored like this: "http://xx0opt02.corpuk.net/ABC_REPORTS/DATA/Table1.csv"
(Columns are identical)
This .csv contains a rolling 7 days of stats. which means if it is added every Morning 6 of those Days will have been added before and must be Deleted.
I would like to schedule an automatic procedure to create a temp table in the server every day and a script removing duplicates.
I have a few questions:
*) Can I shedule from the Enterprise Console to read/create table from the above link to do this?
**) I heard I need an SQL agent. If so why and what is it?
***) Is it better to append data and then script removing Duplicates, or is it better to import into a temp table run comparison between the 2, Delete from Temp what is Common and then append whats left of the Temp to the Table1?
****) Please could someone paste a sample of CREATE TABLE from a http link like the one above?
Thanks for your help,
Gezza
I am getting frustrated with the accounting database table structure. I am really struggling to get the ‘par hours’. This is the amount of time that someone is assigned to work based on their workgroup in the workhour table.
Table 1 –Workhour table
Hours Resourceid workhourgroupcode dayid
NULL TJOHNSO TJOHNSO 1
8.0 TJOHNSO TJOHNSO 2
NULL TJOHNSO TJOHNSO 3
8.0 TJOHNSO TJOHNSO 4
NULL TJOHNSO TJOHNSO 5
8.0 TJOHNSO TJOHNSO 6
NULL TJOHNSO TJOHNSO 7
Table 2 - Time
Work DayHours
WorkGroup Resourceid DayID Non TimeDayWorked Hours worked
NULL SMB ABOOKWALTER 1 NULL 2007-12-26 00:00:00.000 7.50
NULL SMB ABOOKWALTER 1 NULL 2007-12-27 00:00:00.000 7.50
NULL SMB ABOOKWALTER 1 NULL 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 2 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 3 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 4 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 5 1.00 2007-12-28 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKWALTER 6 1.00 2007-12-28 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-26 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-27 00:00:00.000 7.50
NULL SMB ABOOKWALTER 7 NULL 2007-12-28 00:00:00.000 7.50
When I link these together by the resourceid dates worked, but since there is no date in the workhour table I cannot even do an outer join. The only think I could think of was creating a temp table (or a view) to return a line item for every day during the timeframe given. I created a dayID for they second table, I am not sure how to proceed. I need a line item to return for every day of the month so it will look at the workhours table to see how many hours someone is supposed to work.That will allow me to bring into Crystal, group and total.
8.30 SMB ABOOKW 6 1.00 2007-12-26 00:00:00.000 7.50
8.30 SMB ABOOKW 6 1.00 2007-12-27 00:00:00.000 7.50
8.30 SMB ABOOKW 6 1.00 2007-12-28 00:00:00.000 7.50
NULL SMB ABOOK 7 NULL 2007-12-29 00:00:00.000 0
NULL SMB ABOOKW 7 NULL 2007-12-30 00:00:00.000 7.50
NULL SMB ABOOKW 7 NULL 2007-12-31 00:00:00.000 0
Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go
Alter Procedure spReport_SomeFooReport
@SearchFromThisDate datetime = null, @SearchToThisDate datetime = null
As
Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)
set @TableUniqueIdentifier = newid()
set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
exec @SQLString
Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go
-------------------------------------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.
So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00
hello friends!!
i am trying to create stored procedure but i am getting error
create proc t
@i int
as
if @i = 1
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'SS' s) p
end
if @i = 2
begin
select s Name,identity (int,1,1) as intid into #T
from
(
select 'S' s) p
end
Server: Msg 2714, Level 16, State 1, Procedure t, Line 15
There is already an object named '#T' in the database.
Server: Msg 170, Level 15, State 1, Procedure t, Line 17
Line 17: Incorrect syntax near 'p'.
T.I.A
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
View 5 Replies View RelatedI need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
View 16 Replies View Relatedi am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies View Relatedit runs over night, pulls 10.5m rows. Inserts into a table, from a select (so "insert...select", rather than "select into"), from many tables, grouping on a max. It's complex. During the day, it runs fine - maybe 25 minutes. At night it *sometimes* runs fine, but then sometimes takes 4hours.Checking this morning there were 230 threads open for this one query. Checking sys.dm_os_tasks and sys.dm_os_waiting_tasks there were no other wait types on that session_id. None at all. Checking activity monitor, most of the existing threads were suspended on the insert.
There are 24 cores, but NUMA'd. We have maxdop on the server of 8. The maxdop option on the query is 12, just to speed up the select. Index and Stats refreshed daily. We've eight identical tempdb data files, on a separate spindle. Checking those, they are filling up using the round robin correctly
Would the delay be due to SQL trying to combine so many 'select' threads into one 'insert' thread (as it can't insert in parallel; 2014 can, apparently. Upgrades not available!)Should i change the SP to run the select into a temp table (table variable?) with a maxdop of 12, then do the insert into the actual table using a maxdop of 1. Checking the execution plans for a table variable implies the subtree cost comes down from 2.5m to 357k. What's best - temp table or table variable?
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
I try to modify a code to create a temp table but got an error.
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
[code]...
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies View RelatedI am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
[Code] .....
In Access, I can import a txt file data(e.g. Claims.txt) as below specifications:
Choose the delimiter that separates your fields: Other (|)
First row contains field name: Yes
Text Qualifier:"
I need to create a store procedure to read txt file data (d:cliamcliams.txt) first column (ClaimNumber) into a temp table (#claim)
(This #claim table will use for my .Net program)
There about 5 txt files need to process every day.
I have created a global temp table in Step1 of SQL Job.
I have used that in remaining steps of same job...i ran the job
But i got error message like invalid object name ##xxxxxxxx later i have included as tempdb..##xxxxxxxx also. the i got invalid reference for...
From my SSMS:-
But i was able to do select query for the same from my SSMS...
i have incorporated all steps in single step and completed job...
My question is why ##temp table created in step1 is not able to use in other steps of same job ?
SQL Server 2012 Enterprise Edition
I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.
drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint
For some reason I cant not get 2015 data .
I am having with trying to import XLSX files into SQL 2012 64 Bit.
I have installed the Access driver (AccessDatabaseEngine_x64.exe)
I have configured the script to run the following SP
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
[Code] ....
So I first create my Temp Table
The run the SP above then I run the insert into the Temp table defined
INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')
[Code] ....
The error message I get back is
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What I have set wrong on the import? Using SSIS at this point is not a real option.
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
View 0 Replies View RelatedI have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
[Code] ....
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.
Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] .....
The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] ....
The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp
-------- ---------
11 *
12 *
ProdGrp ProdGrpDesc
--------- ---------------
A Prod Group A
B Prod Group B
C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp
-------- ---------
11 A
11 B
11 C
12 A
12 B
12 C
I have a fact table with few flag columns.
What is the best way to bring them to dimension?
Do I need to create dimension(dummy) from fact table for each flag or all flags in single dimension?
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
[Code] ....
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work?
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:
2013 2014 2015
cnt1 cnt2 cnt1 cnt2 cnt1 cnt2
jan 5 77 77 8 88
etc....
Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.
create table #raw1 ([yr] int, [mnth] int, [cnt1] int)
insert into #raw1 values
(2013, 1, 5)
, (2013, 2, 5)
, (2013, 3, 5)
, (2013, 4, 5)
, (2013, 5, 5)
, (2013, 6, 5)
[code]....
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber,
CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d
[Code] ....
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well.