Partitioning Table Based On A Non Primary Key Column

Oct 25, 2007

We are facing few issues pertaining to creation of primary key on a non - partitioned column in sql server 2005. Herewith attaching the text file containing the detailed scenario.

Pls advice.


Pls find some of the scenario with the example given below:

We have done the following steps
1.Creating a Partition Function
CREATE PARTITION FUNCTION pf_EncounterODS_StateID (CHAR(2))
AS RANGE RIGHT FOR VALUES
(
'CA', -- CA
'MI', -- MI
'NM', -- NM
'OH', -- OH
'TX', -- TX
'UT', -- UT
'WA' -- WA
)
GO
2.Creating a Partition Schema
CREATE PARTITION SCHEME [ps_EncounterODS_StateID]
AS PARTITION pf_EncounterODS_StateID TO
(
[PRIMARY],
[ENC_DM_DATA_01], -- CA
[ENC_DM_DATA_03], -- MI
[ENC_DM_DATA_04], -- NM
[ENC_DM_DATA_05], -- OH
[ENC_DM_DATA_06], -- TX
[ENC_DM_DATA_07], -- UT
[ENC_DM_DATA_02] -- WA
)
GO

3.Creating a table using above partitioned schema

CREATE TABLE [dbo].[Fact] (
[ValidationErrorSID] [bigint] NOT NULL IDENTITY(1,1),
[ProcessID] [int] NULL ,
[StateID] [char] (2) NOT NULL ,
[ClaimID] [char] (15) NULL ,

) ON [ps_EncounterODS_StateID](StateID)

4.Creating a primary key on validationErrorSID column in the fact table
ALTER TABLE [Fact] ADD CONSTRAINT NQValidationError PRIMARY KEY CLUSTERED ([ValidationErrorSID])
Step 4 throws an error as
---------------------------------
Column 'StateID' is partitioning column of the index 'NQValidationError'. Partition columns for a unique index must be a subset of the index key.
If we include StateID along with ValidationErrorSID for index,then it works fine.But we need to have only ([ValidationErrorSID]) for indexing.

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Table Partitioning Based On Date Column

Aug 25, 2014

We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.

I need 3 partitions

First partition has to hold present data
second partition need to hold the previous year data (SAS storage)
Third partition need to hold all the old data and need to be in the archive database

I understand that first we need to disable the constraints (Indexes PK & FK)
Then create partition function and partition schema
Then Create the Constraints again

View 9 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related

Sql Table Data Stroring Based On Varchar Primary Key

Jan 17, 2008

Hi,

I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique.




The question I have is regarding how SQL Server actually stores data:

I see that when I add users, they are always stored alphabetically sorted on username.

I was expecting that all users will appear on the users table in the order they were added.

Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'

If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table.


My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?


If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move.


Bottom line, insert, delete will be very expensive.

I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.

Thanks

View 2 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

One Primary Key To Two Column In Same Table

Feb 9, 2015

I have a table name Dispatch with the following column
ID,Date, Name, Pickup, Going

I have a next table called Move with the following column
ID, MoveName

I link Move table with Dispatch table via relationship.
ID_Pickup, ID_Going

upon running query no data is shown unless i remove one of the column form the query either pickup or going ...

View 7 Replies View Related

How To Knw Which Column Is Primary Key In A Table

May 1, 2006

hi all

my question is which query shud i use in sql server 2000 to get which column or columns are primary keys of table

i dont want to use any stored procedures only sql query

sp_primary_keys_rowset is one of d stored proc in sql server 2005 but i couldn't understand which query they are using

i only want to use sql query

View 3 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related

Adding Column With Primary Key In Existing Table

Jan 21, 2014

I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table

When I am using this query:

alter table hem154
add indexNO uniqueidentifier default newid()

alter table hem154
add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO)
go

Note:
Hem154 ~ Table name
indexNo ~ Column Name

I get this runtime error:

Msg 1779, Level 16, State 0, Line 1
Table 'hem154' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

View 4 Replies View Related

Creating A New Numbered Column As Primary Key In A Table

Jul 23, 2005

This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?

View 1 Replies View Related

SQL Server 2012 :: How To Add A Primary Key For Existing Column In The Table

Oct 19, 2015

How to add a primary key for existing column in the table

View 8 Replies View Related

DB Design :: Add A New Int Identity Column As Primary Key To Already Existing Table

Sep 3, 2015

We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:

CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,

[code]....

Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?

View 4 Replies View Related

Update Table Column Based On Value From Another Table?

Sep 2, 2005

Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ď?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]

View 1 Replies View Related

DB Design :: More Than One Table Primary Key Adding In Single Foreign Key Column

Nov 6, 2015

I have come up with one scenarios where I have three table like Product, Services and Subscription.  I have to create one table say Bundle where I can have some of the product id , service id and Subscription id ,  i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?

View 3 Replies View Related

Help On Partitioning Column Was Not Found.

May 31, 2007

Hi,I don't know if I missed anything. I have 2 member tables and onepartition view in SQL 2000 defined as followingCREATE VIEW Server1.dbo.UTableASSELECT*FROMServer1..pTable1UNION ALLSELECT*FROMServer2..pTable2CREATE TABLE pTable1 ([ID1] [int] IDENTITY (1000, 2) NOT NULL ,[ID2] [int] NOT NULL ,...<other columns>.........CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED([ID1],[ID2]) ON [PRIMARY] ,CHECK ([ID2] = 1015)) ON [PRIMARY]CREATE TABLE [pTable2] ([ID1] [int] IDENTITY (1001, 2) NOT NULL ,[ID2] [int] NOT NULL ,...<other columns>.........CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED([ID1],[ID2]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CHECK ([ID2] <1015)) ON [PRIMARY]SELECT is working fine. However, I got error message if I issue anupdate command such asUPDATE UTableSET somecol = somevalWhere somecol2 = somecondServer: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'UTable' is not updatable because a partitioning columnwas not found.Anyone have any idea? ID2 is my partition column, why the SQL 2Kdoesn't see it. It is a part of primary key, having checkingconstrain, and no other constrain on it. Am I missing something?Thanks a lot.

View 8 Replies View Related

Partitioning Column Was Not Found

Feb 14, 2008

CREATE VIEW [dbo].[pv_mainQAStatus] as

SELECT

[QAAccountID], [FunctionalAreaID],

[EvaluationStartDate], [EvaluationEndDate]



FROM

[PA_mainQAStatus]

UNION ALL

SELECT

[QAAccountID], [FunctionalAreaID],

[EvaluationStartDate], [EvaluationEndDate]

FROM

[CS_mainQAStatus]

UNION ALL

SELECT

[QAAccountID], [FunctionalAreaID],

[EvaluationStartDate], [EvaluationEndDate]

FROM

[GV_mainQAStatus]

In all the 3 tables I have QAAccountID and FunctionalAreaID as Primary key. I am getting this error when I try to insert

'UNION ALL view 'pv_mainQAStatus' is not updatable because a partitioning column was not found.'
How to over come this. I am used to writing an INSTEAD OF Trigger.

View 7 Replies View Related

How Can I Hide A Table Column Based On A Group ?

Jul 12, 2007

I am using Reporting Services 2000. If you find out that Reporting Services 2005 would resolve this issue, please lemme know also. But I want to mention that I would prefer a way to fix this without changing Reporting Services versions.

I have a table that has a group on ProductTypes. This group is set to PageBreak at end.
What I need to do is to conditionally hide an entire column based on the current group's ProductType.

Examples
Page 1 = Product 1 = Column 1 is visible
Page 1 = Product 1 = Column 2 is visible
Page 2 = Product 2 = Column 1 is hidden
Page 2 = Product 2 = Column 1 is visible

Can you help me figure this one out ? I've tried everything I found on the net, especially everything on this page : http://blogs.msdn.com/chrishays/rss.xml

Thank you in advance.

View 2 Replies View Related

SELECT INTO A New Table All Columns Based On DISTINCT Value Of One Column

Oct 31, 2014

‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:

SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’

View 4 Replies View Related

Adding A Column Name To A Table In Each Of The Databases Based On A Condition

Oct 3, 2007

i have the folowing databases DB1,DB2,DB3,D4,DB5........

i have to loop through each of the databases and find out if the database has a table with the name 'Documents'( like 'tbdocuments' or 'tbemplyeedocuments' and so on......)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.


can someone show me the script to do it?


Thanks.

View 3 Replies View Related

Transact SQL :: Populate Column On Insert Based On Value From Another Table

Jul 26, 2015

I have the following 2 tables:

Table: classes  Columns: classID, hp
Table: char_active  Columns: name, classID, hp

The classes table is already populated.

What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error

Incorrect syntax near 'inserted'.

I'm new to sql, this is actually the first trigger I've tried writing. 

create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go

View 4 Replies View Related

Trigger To Update Table Based On COUNT Of Column

Sep 26, 2007

Hello again,

I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.





Code Snippet
CREATE TABLE #Assignment
(CallID INT IDENTITY(1500,1) PRIMARY KEY,
AssignmentGroup VARCHAR(25),
Assignee VARCHAR(25)
)
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')

GO

SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee






What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.


So, the logic for the trigger would be

UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))


But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.

Does any one have an idea or pointers as to how I should go about this?

Grateful for any advice, thanks
matt

View 5 Replies View Related

Getting Data Based On Ordinal Position Of The Column In The Table

Aug 30, 2007

Hi
I am having a problem in auditing the column data in tables.My requirement is i have write a trigger which is capable of auditing the columns which are going to be added in the future also with out using dynamic SQL.is there any way to do so.
I feel if i can get the column data based on ordinal position then it is possible.
Can any body suggest.
My set Up is like this
I have a base_table to be audited.
I have a Audit_spec table which contains name of the table and columns to be audited.
And Audit table which actually captures the table name,column name ,old value and new value.
I have to audit only those columns in the Audit_spec spec.
If schema changes(Like new column added) happens to base_table and I want that column to be audited.with out any changes to my trigger code i should handle the newly added column ..





View 6 Replies View Related

SQL 2012 :: Query Based On Column Name / Setting Up Database Table

Oct 30, 2015

I have a set of data spread across a number of tables regarding stock market data. An example of this follows:

Market Capitalization...

Date CompA CompB
01/01/11 100 5
02/01/11 102 4

Share Price....

Date CompA CompB
01/01/11 100 100
02/01/11 101 99

Event Data...

Date Company
01/01/11 CompA
02/01/11 CompB

Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.

I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.

For instance:
SELECT Event.Date, Event.Company
FROM Event

how do I now say.....

SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.

I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).

I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.

View 1 Replies View Related

Selecting Rows From A Table Based On First 2 Characters Of 12 Char Column

Oct 21, 2013

I have to select rows from a table

if the first 2 characters of a 12 char column are
'GB'

Select BFKEYC from table where

I have a hokey way of doing it but it looks embarrassing:

BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'

View 8 Replies View Related

Transact SQL :: Update A Column Randomly Based On Values From Another Table

Oct 27, 2015

I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work

DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000

[code]...

View 8 Replies View Related

Transact SQL :: Update Table With Max Value And Row Number (based On 2 Column Partitions)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cd permit_nbr reference_nbr

ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008

View 3 Replies View Related

Slowly Changing Dimension Wizard Doesn't Display Destination Table Primary Key Column

Feb 22, 2006

I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it.  After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors:
1. The wizard hangs for several minutes before displaying the columns from that table.
2. The wizard does not display the primary key column.  This, of course, is the column I want to designate as the "business key", but can't because it's not displayed.
I know this is more like a fact table than a dimension, but this is not a data warehouse.  This is just a very large table, and I need to update a field in certain records based on the contents of a source text file.  Is there another transformation I should use to perform updates?

View 2 Replies View Related

Query To Update Flag Column In Second Table Based On Adder Names

Sep 11, 2013

I want to update Flag column in second table based on the Adder names.

If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.

APpName OS Adder

App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql

App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB

OUTPUT SHOULD BE LOOK LIKE BELOW

APpName OS Adder Flag

App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null

App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE

View 5 Replies View Related

Power Pivot :: DAX Formula For Extracting A Column Value From Table Based On Certain Conditions

Jul 20, 2015

I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

Here is the scenario:

I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

I would like to extract  Key_member where key_member_type="PM" and active as of tb1(month_end_date).

Is this possible using DAX ?

View 6 Replies View Related

T-SQL (SS2K8) :: How To Vary Column Names In Cross Apply Based On Different Columns In Each Table

Feb 26, 2015

I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:

select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank

[code]....

Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:

GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)

[Code] ....

These are imported from an Excel Workbook so that's why all the columns with spaces for now.

View 9 Replies View Related

SQL Server 2014 :: How To Update Values Based On Column Into Multiple Columns In Another Table

Jul 31, 2015

I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.

CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE

One way to achieve this is to write two update statements. After update, the output you see is my desired output

UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output

my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.

View 5 Replies View Related

Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?

Nov 30, 2007



I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.

each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.

Ideas?
Thanks,
Ad.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved