Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables

Easy Q.. Query Without Temp Table
How do I rewrite this query so that I do not use a temp table?
 

SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

View Replies !

---

Cursor Or Temp Table
I just want to know which one is more efficient cursor or temp table for looping through a record set?

I am basically looking for better performance and server utilization.

View Replies !

---

Cursor Or Temp Table
Hi Chaps,

I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table.

I am just wondering that should I use Cursor or TEMP table (caz of performance issues)?

As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client)

Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY?

Note: I am using SQL Server 2005 standard edition

waiting for quick reply.

 

regards,

Anas

View Replies !

---

Cursor On A Temp Table
Hi all,

I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again.
I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table?

Necessity is the mother of all inventions!

View Replies !

---

Cursor To Store Value In Temp Table
Following sp gives wrong result whats wrong with following cursor?

ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]

@ProjectBenefitID INT

AS

SET NOCOUNT ON


DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)

DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )


DECLARE @AssessBenefitID INT
DECLARE @ActualQuantity INT
DECLARE @ExpectedQuantity INT
DECLARE @ActualQuality VARCHAR(2000)
DECLARE @ExpectedQuality VARCHAR(2000)
DECLARE @AssessFlag CHAR
DECLARE CUR_BENEFIT CURSOR FOR
SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID

OPEN CUR_BENEFIT


FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag




WHILE @@FETCH_STATUS = 0
BEGIN



SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID



INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)


FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END

CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT


SELECT * FROM @TEMP_BENEFIT

View Replies !

---

Increment In Select Query Without Cursor And Temp Table
I have:

Select
T0.Id_Roomtype,
count(T0.ID_Room)as Total,
convert(smalldatetime,'20000601') as ADate --- !!!!
from rmRoom T0
where
T0.id_Property = 1
group by T0.Id_Roomtype

How to do:


declare @x int
set @x = 36901
Select
T0.Id_Roomtype,
count(T0.ID_Room)as Total,
convert(smalldatetime,(set @x = @x+1)) as ADate --- !!!
from rmRoom T0
where
T0.id_Property = 1
group by T0.Id_Roomtype

I am trying to increment value in Column ADate
so to get new date for each row


Is it possible without using cursor and temp table ?

ht

View Replies !

---

Find The Datatypes That Where Used To Create A Temp Table
Is there a way to find out what the datatypes of a temp table are?

Example:

select cust_code, cust_name, cust_state
into #customers
where cust_state = 'TX'


I would like to know what datatypes SQL used when creating #customers.

Thank you for all the help.

View Replies !

---

Calling Column Names Of Temp Tables
 

Hi All,
 
 
I'd like to be able to use column names in my temp table as a join to dynamically create a crosstab (I know about PIVOT).  I was wondering if anyone knows how to access column names and use them from within my query.
 
 

Thanks,
 
Flip'd
 
 
 
 
 
 

View Replies !

---

Big Table(?) Or Split Between Tables?
Hi Guys

I have an application that runs on several sites that has a table with 36 columns mostly ints och small varchars.

I currently have only one table that stores the data and five indexes and since the table on one location (and others soon) has about 18 million rows I have been trying to come up with a better solution (but only if needed, I dont think I have to tell you that I am a programmer and not an dba).
The db file size with all the indexes is more then 10gb, in it self is not an problem but is it a bad solution to have it that way?

The questions are:

Are there any big benefits if i split it into several smaller tables or even smaler databases and make the SPs that gets the data aware that say 2006 years data is in table a and so on?
Its quite important that there are fast SELECTS and that need is far more important then to decrease the size of the database file and so on.

How many rows is okay to have in one table (with 25 columns) before its too big?

Thanks in advance.

Best regards
Johan, Sweden.

CREATE TABLE [dbo].[Cdr](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Abandon] [varchar](7) NULL,
[Bcap] [varchar](2) NULL,
[BlId] [varchar](16) NULL,
[CallChg] [varchar](6) NULL,
[CallIdentifier] [uniqueidentifier] NULL,
[ChgInfo] [varchar](5) NULL,
[ClId] [varchar](16) NULL,
[CustNo] [smallint] NULL,
[Digits] [varchar](32) NULL,
[DigitType] [varchar](1) NULL,
[Dnis1] [varchar](6) NULL,
[Dnis2] [varchar](6) NULL,
[Duration] [int] NULL,
[FgDani] [varchar](13) NULL,
[HoundredHourDuration] [varchar](3) NULL,
[Name] [varchar](40) NULL,
[NameId] [int] NOT NULL,
[Npi] [varchar](2) NULL,
[OrigAuxId] [varchar](11) NULL,
[OrigId] [varchar](7) NULL,
[OrigMin] [varchar](16) NULL,
[Origten0] [varchar](3) NULL,
[RecNo] [int] NULL,
[RecType] [varchar](1) NOT NULL,
[Redir] [varchar](1) NULL,
[TerId] [varchar](7) NOT NULL,
[TermAuxId] [varchar](11) NULL,
[TermMin] [varchar](16) NULL,
[Termten0] [varchar](3) NULL,
[Timestamp] [datetime] NOT NULL,
[Ton] [varchar](1) NULL,
[Tta] [int] NULL,
[Twt] [int] NULL,
[DateValue] [int] NULL,
[TimeValue] [int] NULL,
[Level] [varchar](50) NOT NULL CONSTRAINT [DF_Cdr_Level] DEFAULT ('x:'),
CONSTRAINT [PK_Cdr] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

View Replies !

---

T-SQL To Split Data From One Table Into Two Tables?
What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.


-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)

-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)

View Replies !

---

How To Split Out Table Rows Into 3 Tables
I imported all rows of my txt file using SSIS 2005 into a table.  I am now trying to figure out how to split out the header, payment rows, and maintenance rows.  First, some information.

An example of table results is here:
http://www.webfound.net/split.txt
The table has just one field of type varcha(100) because the incoming file is a fixed length file at 100 bytes per row

The header rows are the rows with HD in them...then followed by detail rows for that header (see here http://www.webfound.net/rows.jpg).

I need to

1) Split out the header into a header table
2) Split out the maintenance rows (related to the header) into a maint table
3) Split out the payment rows (related to the header) into a payment table

I'll need to maintain a PK/FK relationship between each Header and it's corresponding maint and payment rows in the other 2 tables.

To determine if it's a payment vs. maintenance row, I need to compare chars 30 - 31.  If it contains 'MT' then you know it's a maintenance row, else it's a payment row.

How in the hell do I do this???

View Replies !

---

Split Data Into Two Column Table
Hello all,

Little layout question. Assume my dataset returns the following data:

A

B

C

D

E

 

How can I present this data in a table (or list, or matrix) splitted into two columns:

A     B

C     D

E     

 

Any idea will be very appreciated! Thanks a lot!

TG

View Replies !

---

Comparing A Column List Split To A Table.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo

View Replies !

---

DTS Table From Access To SQL, Identity Column Error. Should Be Easy, But I Can't Figure It Out.
 

I am trying to move data from Access to SQL Server 2000 using DTS.
 
I have an Access Source and SQL Server Desitination, My destination table has a field called tableID that is not in the source.  TableID is a Primary Key and an Identity column.
 
I have Enable Identity Insert checked in the options of the Transform Data Task. 
 
When I execute ythe task, I get the error
 
"Cannot insert the value NULL into column 'TableID'.  Does not allow nulls.  Insert Fails.
 
Does anyone know why this simple task would fail?
 
Mike
 
 
 

View Replies !

---

Find Tables With Column Name
How can I find all the tables with a specific column name?

View Replies !

---

Table Variables Vs. Temp Tables
How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead.
Short of testing the stored proc both ways, how do I know what to do?
declare @Temp table
or
create table #Temp

View Replies !

---

Temp Tables Vs Table Variables
I am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you

View Replies !

---

Temp Tables Vs. Large Table
I have a few hundred users, maybe a dozen or two active at any given time, accessing the same database via ASP. The database has many tables, one being a very large orders table with a few million records, in which I have created a view against. A view only because I need to allow the user to filter quite extensively against the results. The users typically only need to view records for the last 30 days and results for each user might be five thousand records or less.

My question is this. Would I be better off writing each user's resultset to a temp table for that user's session and allow the filtering and sorting by the user go against that temp table and increase my hardware requirements to accomodate that. Possibly to the point of creating a database cluster. OR would I be better off leaving it as is where each users uses the same view.

FYI...each user may need visibility to only a hand full of fields, but over all the view must maintain many fields.

Any thoughts on this would be greatly appreciated. Thanks in advance.

Dave

View Replies !

---

Unions Two Tables Into A Temp Table
How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?

View Replies !

---

Find Out What Tables Contain A Specific Column
I want to write SQL that will search the tables in a database for a specific column, like this.  For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in.  It is always the first column.

I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.

I would surely appreciate if someone else has already done this!

Thanks!

View Replies !

---

Find Common Value In Column For 30 Tables
Hi,

I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:

Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.

Any suggestions would be much appreciated,
Jeff

View Replies !

---

Add A New Column In Temp Table In Sp Cannot Be Used Immediately
I created a temp table in my stored procedure and then added a new identity column to it.
However, I am not able to use this new column immediately, it says column not found.
SELECT * INTO #temp FROM table_name
ALTER TABLE #temp ADD  __Identity int IDENTITY(1,1)
SELECT * FROM #temp WHERE __Identity >= 10
Here __Identity column is not found. If I just did SELECT * FROM #temp without the where clause, the final result does have the __Identity column correctly added to the table. Why can't I query it?
Thanks!
 

View Replies !

---

Identity Column In Temp Table
Hi,

I am trying to create a temp table with an identity column.  Here is the code that I am using...

SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1

I am getting an error though.

Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.

Is there any way to work around this? 

Thanks for your help.

View Replies !

---

Temp Table Column Type?
can anyone help me figure out why when i run the following storedprocedure i get the error:(1460 row(s) affected)Msg 245, Level 16, State 1, Procedure SP_SALESTRENDS, Line 40Conversion failed when converting the varchar value 'X' to data typeint.SP:--STORED PROCEDURE FOR INVOICE TRENDS:--To use Stored Procedure use the following code:--EXEC SP_INSPECTIONSUMRY (MONTH), (OFFICE)--(OFFICE) CAN BE: BGR FOR BANGOR, SP FOR SOUTH PORTLAND, NH FOR NEWHAMPSHIRE, UNH FOR UNH--(REPORT) CAN BE: PRODUCT CODE FOR REPORT BROKEN OUT BY PRODUCT CODE-- EXEC SP_SALESTRENDS BGR, INVOICED, 2006, XALTER PROCEDURE SP_SALESTRENDS@OFFICE VARCHAR(30),@REPORT VARCHAR(30),@VARYEAR INT,@CODE VARCHAR(30)ASIF @REPORT='INVOICED'SELECT YEAR(I.INVOICEDAT) AS VARYEAR, MONTH(I.INVOICEDAT) AS VARMONTH,SUM(I.STOTAL) AMOUNT, P.PERSON, P.PRODUCT, C.DESCRIPTNINTO #TEMP_SALESTRENDSFROM OPENQUERY(PROJECTS, 'SELECT PROJECT, INVOICEDAT, STOTALFROM INVSUMYR') ILEFT JOIN(SELECT *FROM OPENQUERY(PROJECTS, 'SELECT NUMBER, PRODUCT, PERSONFROM PROJMAST')) PON (LTRIM(I.PROJECT)=LTRIM(P.NUMBER))LEFT JOIN(SELECT PC, DESCRIPTNFROM OPENQUERY(PROJECTS, 'SELECT PC, DESCRIPTNFROM PRODCODE')) CON (C.PC=P.PRODUCT)GROUP BY YEAR(I.INVOICEDAT), MONTH(I.INVOICEDAT), P.PERSON, P.PRODUCT,C.DESCRIPTNORDER BY VARYEAR, VARMONTH-- INVOICED REPORT BROKEN OUT BY OFFICEIF @REPORT='INVOICED' AND @CODE=1 AND @VARYEAR=1234 AND@OFFICE='NORRIS'SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @CODE!=1 AND @VARYEAR=1234SELECT VARYEAR, VARMONTH, SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT=@CODEGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED'AND @CODE!=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT=@CODE AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NORRIS' AND @CODE=1 AND@VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='BGR' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('G', 'H', 'I', 'J', 'K', 'L') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='SP' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('A', 'B', 'C', 'D', 'E', 'C', 'S', '3', '4') ANDVARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='NH' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('W', 'X', 'Y', 'N', 'O', 'P') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('U', 'Z', 'R', 'V')GROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTHIF @REPORT='INVOICED' AND @OFFICE='UNH' AND @CODE=1 AND @VARYEAR!=1234SELECT VARYEAR , VARMONTH , SUM(AMOUNT) AS AMOUNTFROM #TEMP_SALESTRENDSWHERE PRODUCT IN ('U', 'Z', 'R', 'V') AND VARYEAR=@VARYEARGROUP BY VARYEAR, VARMONTHORDER BY VARYEAR, VARMONTH--END OF SALES TRENDS STORED PROCEDUREthanks.

View Replies !

---

Changing Column's Name In A Temp Table
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!

View Replies !

---

Create A View Or Temp Table From 2 Tables
 

I have 2 tables:
 

Customer Table: ID, OrderID (composite key)

100, 1
100, 2
200, 3
200, 1
Order Table:  OrderID, Detail

1, Orange
2, Apple
3, Pineaple
 

Assuming each customer always orders 2 items. I need to create a SQL query that shows as following (a view or a temp table is OK). How do I do that?

 
 CustomerID, Order Detail1, Order Detail2

 100, Orange, Apple
 200, Pineaple, Orange 

 
 
 

View Replies !

---

How To Find All Tables That Have An Identity Column As A Primary Key
 How do i find all the tables that have an Identity Column as a primary key in a database. Thanks.

View Replies !

---

Adding A Column To A Resultset In A SP WITHOUT Using A Temp Table
I have the following tablestblGroupsGroupID intGroupName nvarchar(50)tblGroupMembersGroupID int (FK)UserID intI need a stored proc which:returns the groupID and name of  all the groups of which userid 5 is a member AND also return the number of members that a group has (so the numbers of records in tblGroupMembers with a specific groupID)I have 2 sp's:myspGetGroupMembersCount which takes as input a groupID and has as output an integer valuemyspGetGroupsforUser which must return the entire resultsetSo say that userID 5 is a member of GroupID 6,18 and 22the following must be the resultset:UserID GroupID GroupName Members5          6             bla           132            5          18            yes          17             5          22           whatever    200                 I think I need to call myspGetGroupMembersCount from within myspGetGroupsforUser and add it to the resultset (I dont want to work with a temptable)...but I dont know how...

View Replies !

---

Select From Multiple Tables, Insert In Temp Table
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?


Any ideas?

Thanks in advance.

View Replies !

---

Need Help In Copying A Temp Tables Contents To A Existing Table
I have a real table with an identity column and a trigger to populate this column.

I need to import / massage data for data loads from a different format, so I have a temp table defined that contains only the columns that are represented in the data file so I can bulk insert.

I then alter this table to add all the other columns so that it reflects all the columns in the real table. I then populate all the values so that this contains the data I need.

I then want to insert into the real table pushing the data from the temp table, however this gives me errors stating that the query returned multiple rows.

I specified all the columns in the insert grouping as well as on the select from the temp table.

ANY thoughts / comments are appreciated. This is beginning to drive me nuts.

 

Rob

View Replies !

---

How To Make A Temp Table Using Info Fromtwo Tables
I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo.
StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.

GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.

What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.

Never assume someone knows what you are talking about.

View Replies !

---

Need To Find Instances Of Duplicates Within A Column; Joining 2 Tables.
My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch

View Replies !

---

SQL Question: Change Column Names Of Temp Table In SP
Hi,
I have a SP which returns a select query on a temp table so I get to choose column names
when I create the #table.

Can I determine column names myself based on the results of another query (in the SP)
before, or (preferably) after I create the #table and populate it.

My query is used to bind to a datagrid so I could use....

dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want
to keep the code in the SQL.

Cheers

View Replies !

---

'One Table' Record To Separate 'two Or Three Tables' Using Cursor?
I would like to 'one table' record to separate 'two or three tables'
. I just know use the DTS , try to import and export again and agian.
So trouble.

Could you give me some suggestions for me? For example ,
'Cursor' write in new table . But I try to SQL Server Books Online
which is not suitable for me solving problems. One table separate two
or three tables. Can you wirte the detail example for me?
Thx a lot.

View Replies !

---

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?
Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View Replies !

---

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL
 
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name.  Some tables have slight differences in terms of data types or Data type lenght.
 
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
 
I am using SQL Server 2005.  And the Two Servers are Linked Servers 
 
What Script can i use to accomplish this task. Thanks
 
 

 
 

 

View Replies !

---

Split Function With Cursor
Hi,
I am trying to write a stored procedure that takes a comma separated letter. I have a split function that returns the splitted letters. I have select some values from the tables in the database where the title starts with each splitted letter. I thought I should use cursor that contains each letter and in the while loop i put my select statement with the conditions.
Is this the correct way. Or are there any ideas for this?

thanks,

Regards,
shakthi

View Replies !

---

&"Column Name Or Number Of Supplied Values Does Not Match Table Definition&" When Trying To Populate Temp Table
Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View Replies !

---

Find A Column In All The Table
Hi,
I need to find the text data type column in all the table.
Please let me know the script.
Thanks,
Ravi

View Replies !

---

Find All Tables That A Table Is Related To
how can i find all the names of tables that a specific table is related to in tsql?

View Replies !

---

Find Column Name And Datatype Of A Given Table
Hi,

How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.

Thanks
-Sudhakar

View Replies !

---

Find Table And Column Name From Data
I need to find a table and column name from some given data. I know what data i want to edit, I just need to know where it is located and the database is too big to manually go through. It is Microsoft sql server 2000. Any help is appreciated.

View Replies !

---

Is It Possible To Find Out When A Particular Column Was Added To A Table?
Is it possible to find out when a particular column was added to any table?

Thanks

View Replies !

---

How To Find Column Size In A Table?
 
Is there any way to get size of the individual column in a table?
 
I know we can use sp_spaceused to get the size of the table. But my question is diiferent. I have a table with 50 columns and approx 2 million rows in it. I wanted to know which column is taking most of the space.
 
Thanks
 
 
 

View Replies !

---

Find String In Any Column In Any Table
quote:Another question is how to find a string in any column of any table.
(Above is a hint as to one way)
I had to do this because a system was sending out a not very impressive email to users ad we couldn't find what was triggering it.


I have run into a similar situation. Care to share your solution?

Cat

View Replies !

---

Easy Way To Find Connection Leaks?
I, just like others, am experiencing problems with data leaks. I was wondering if anyone knows of a tool out there that can help pinpoint the pages with problems? I basically want to find the culprits!


Thanks,
--
Yonah

View Replies !

---