Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Sep 5, 2007
Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID Email
-- ----------
1 AS1 AS11
2 AS2 AS3 AS4 AS5
3 AS6 AS7
The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID EmailAddress
--- ------------------
AS1 abcstu@emc.com
AS2 abcstu2@emc.com
AS3 abcstu3@emc.com
AS4 abcstu4@em.com
AS5 abcstu5@emc.com
AS6 abcstu6@emc.com
AS7 abcstu7@emc.com
AS11 abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?
Many Thanks,
probetatester@yahoo.com
View 7 Replies
ADVERTISEMENT
Aug 7, 2007
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 3 Replies
View Related
Sep 16, 2004
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 6 Replies
View Related
May 18, 2007
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 4 Replies
View Related
Aug 2, 2006
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 12 Replies
View Related
Mar 20, 2008
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 3 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Jun 4, 2015
I have a cte:
With cte as
(
Select distinct Incident_ID,
ACTUAL_SEVERITY ,
Policy
From
table)
There are 3 ACTUAL_SEVERITY value: 1-High, 2-Medium and 3-Low
I need the final result be like:
Policy High Medium/Low
How do I write the script with out using temp table or cursor?
View 6 Replies
View Related
Feb 18, 2008
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 2 Replies
View Related
Sep 29, 2015
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
View 2 Replies
View Related
Nov 20, 2007
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 14 Replies
View Related
Oct 4, 2007
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 6 Replies
View Related
Feb 21, 2005
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 5 Replies
View Related
Mar 14, 2006
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 4 Replies
View Related
Mar 13, 2007
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 4 Replies
View Related
Sep 23, 2015
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
View 7 Replies
View Related
Jun 6, 2005
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 2 Replies
View Related
Mar 8, 2006
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 1 Replies
View Related
Aug 4, 2005
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 2 Replies
View Related
Aug 31, 2007
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 4 Replies
View Related
Dec 15, 2005
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 1 Replies
View Related
Jul 23, 2005
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 3 Replies
View Related
Oct 16, 2006
How can I find all the tables with a specific column name?
View 3 Replies
View Related
Jan 4, 2008
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 10 Replies
View Related
Jun 18, 2007
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 4 Replies
View Related
Jun 19, 2005
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 1 Replies
View Related
Jul 10, 2007
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 7 Replies
View Related
Aug 24, 2006
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 4 Replies
View Related
Mar 21, 2006
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 1 Replies
View Related
Jul 28, 2006
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 6 Replies
View Related
Feb 18, 2004
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 6 Replies
View Related
Mar 8, 2008
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 6 Replies
View Related
Nov 8, 2006
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 5 Replies
View Related
