Calculated Values In Target Table
Dec 12, 2007
Hi,
is there any way to fill up tables with calculated valued? My Source Table is build-on like this example:
CustNo / Year / Variable / Value
100 / 2005 / var1 / 321.90
100 / 2005 / var2 / 44.20
100 / 2006 / var1 / 12.09
100 / 2006 / var2 / 7.91
101 / 2005 / var1 / 23.78
101 / 2005 / var2 / 67.22
My Target Table should have this result:
KPI / CustNo / Year / Value
KPI1 / 100 / 2005 / 366.10
KPI1 / 100 / 2006 / 20.00
...
What I want to accomplish? I want to add var1 and var2 from Customer 100 in Year 2005, but is this possible with standard tasks in SSIS?
bye
Jonas
View 6 Replies
ADVERTISEMENT
Apr 27, 2015
I am trying to exclude records that have an assessed value that has been waived in an aggregation. For Example:
Here is my table:
CREATE TABLE #temptable (ReportingMonth Varchar(6), Fee_Code Varchar(20), Fee_Transaction_Amount Decimal(12,2), Fee_Transaction_Date Datetime, Fee_Transaction_Type Char)
INSERT INTO #temptable (ReportingMonth, Fee_Code, Fee_Transaction_Amount, Fee_Transaction_Date, Fee_Transaction_Type)
SELECT 'Jan-13', 'ONE TIME DRAFT FEE', '20', '01/24/2013', 'A'
UNION ALL SELECT 'Feb-13', 'LATE CHARGE', '33.6', '02/19/2013', 'A'
UNION ALL SELECT 'Mar-13', 'LATE CHARGE', '37.01', '03/18/2013', 'A'
[code]....
Here are Data Mapping Description
Reporting Month = Month - Year
Fee Code = Fee Description Name
Fee Transaction Amount = Fee Amount
Fee Transaction Date = When Fee Amount was Applied
Fee Transaction Type = "A" = Assessed Fee; "W" = Waived Fee; "P" = Paid Fee
I've also included an image with beginning data set and what I want to identify in red and what my final data set should look like after the exclusion of those 4 records are removed.
Here are the logic requirements:In the attachment what I need the logic to do is essentially identify the $20 One Time Draft Fee from the first instance using the MIN Transaction date. Since there were $80 waived for this fee code (One Time Draft Fee), I would expect to see the first 4 (highlighted in red) to be identified as the target and as you can see in the attachment the second data set had the 4 highlighted items removed. That should be my final output.
trying to loop and remove the waive amounts from the assess amounts and tied it back to remove from my base data.
View 1 Replies
View Related
Aug 27, 2007
Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @number
View 2 Replies
View Related
Nov 14, 2006
What is the best code pratice to use do the following code,
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
instead of
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?
View 1 Replies
View Related
Dec 11, 2001
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
View 2 Replies
View Related
Jul 20, 2005
Hi everybody,I need some help on calculated values in my database design.I'm creating an accounting / business management application with theusual modules like A/R, A/P, Inventory, etc.I don't know how to handle calculated values in fields like "CustomerBalance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"and other similar.I don't want to create fields in the database to store these valuesbut I want to create them on the fly instead. Another thing I preferto do is use code in my application (vb.net) not T-SQL to do this(stored procedure or views), because this way I'm moving businesslogic into SQL; so far all the logic is located in a Business RulesDLL.My question is how can I do that and at the same time achieve "easy"joins.For example:Every time I retrieve some customer's info I need his balance. Let'ssay if my query is:SELECT CustomerID, CustomerName, Address from CustomersWhere CustomerID = ‘xxxxxx'How can I retrieve the balance, when is calculated from other fieldsin other tables, such as Invoices, Credit Memos and Cash Receipts?I thought to use views and add a join to the above query. Is that goodor bad, performance wise?What other options I have?Looking forward for your answer.Thanks in advanced.Stefanos
View 3 Replies
View Related
May 22, 2008
Hi all
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null.
Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
Thanks
View 4 Replies
View Related
Jan 24, 2008
Hi,
Does anyone know how the key influencers impact values are calculated? Thanks!!
Kate
View 3 Replies
View Related
Sep 21, 2015
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.Â
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
Brandx         50
   Prod1       20
   Prod2       30
Brandy         60
   Prod5       40
   Prod6       20
View 2 Replies
View Related
Feb 4, 2007
I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
View 5 Replies
View Related
Oct 26, 2015
I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up withÂ
Division
 NewSet
Dollars
AA
CustomType1 Â
$166.64
AA
CustomType3Â
$64.24
BB
[code]....
View 2 Replies
View Related
May 12, 2007
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure
@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)
as
begin
set nocount on
if @opbal IS NULL OR @opbal = 0
begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end
select @accountid = isnull(max(accountid),0) + 1 from accountmaster
select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster
begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode
)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)
end
set nocount off
end
Thanxs in adv...
View 7 Replies
View Related
Apr 23, 2008
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
View 6 Replies
View Related
Sep 13, 2005
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
View 9 Replies
View Related
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 4 Replies
View Related
Jan 22, 2007
Could you tell what's wrong when I split table to the target partition table?USE TEST--ADD FILEGROUP---------------------------------------------------------------------ALTER DATABASE TEST ADD FILEGROUP FG_01ALTER DATABASE TEST ADD FILEGROUP FG_02ALTER DATABASE TEST ADD FILEGROUP FG_03--ADD FILE--------------------------------------------------------------------------ALTER DATABASE TEST ADD FILE (NAME = DF_01,FILENAME = 'D:TESTDF_01.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_01ALTER DATABASE TEST ADD FILE (NAME = DF_02,FILENAME = 'D:TESTDF_02.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_02ALTER DATABASE TEST ADD FILE (NAME = DF_03,FILENAME = 'D:TESTDF_03.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_03--CREATE PARTITION FUNCTION---------------------------------------------------------CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)AS RANGE LEFT FOR VALUES ('20070101 23:59:59.997','20070102 23:59:59.997')--CREATE PARTITION SCHEME-----------------------------------------------------------CREATE PARTITION SCHEME PS_HIS_HTTP_LOGAS PARTITION PF_HIS_HTTP_LOG TO ( FG_01, FG_02, [PRIMARY])--CREATE PARTITION TABLE -----------------------------------------------------------CREATE TABLE HIS_HTTP_LOG ( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON PS_HIS_HTTP_LOG(STARTIME)--INSERT DATA，PARTITION 1 20070101-------------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 100BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())SET @i = @i +1END--INSERT DATA ,PARTITION 2 20070102-------------------------------------------------SET @i = 1WHILE @i <= 200BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())SET @i = @i +1END--CREATE A TABLE -------------------------------------------------------------------CREATE TABLE TMP_HTTP_LOG( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON FG_03--INSERT DATA TO TMP_HTTP_LOG 20070103-----------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 400BEGININSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())SET @i = @i +1END--ADD CONSTRAINT--------------------------------------------------------------------ALTER TABLE TMP_HTTP_LOGWITH CHECKADD CONSTRAINT CK001CHECK (STARTIME >= '20070103 00:00:00.000' AND STARTIME <= '20070103 23:59:59.997')--SPLIT RANGE ,SWITCH DATA----------------------------------------------------------ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3--==========================================�======================================Why is the error in step of“ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3�error infomation：message_id 4972，level 16，severity 1ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.Please tell me why ? check constraints ?Thank you very much !
View 1 Replies
View Related
Oct 4, 2013
I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data.
declare @Stop table (StopId int, UserField varchar(20))
declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))
declare @UpdateSource table (HasPathway varchar(10))
insert into @Stop (StopId, UserField)
values (1, 'Yes')
[code]...
I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.
View 3 Replies
View Related
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
Mar 26, 2001
Having an identity column in target table, how do I use SQL 7 DTS ?
In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...
In DTS package, had Fast load and Enable Indenity checked - no luck !
What would be the standard procs for this since this sounds like a very common
senario.
Appreciate your help very much !
-Ivan
View 1 Replies
View Related
Dec 29, 2014
I have need to insert records when not in the target table.
sample ddl for source and target tables. Notice the target table is missing the 'Upstairs' Rows:
CREATE TABLE source (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)
[code]....
View 2 Replies
View Related
Jul 20, 2005
I want to replicate a database to a subscriber that will be used as a readonly copy. The data has to be replicated as close to instantly as possible.To do this I set up a database export of objects and data to populate thesubscriber, then I set up transactional replication. To verify thatreplication is working successfully, I count the rows in each table, thereare 3 tables in total. For one of the tables, the replication completes butalmost immediately afterward, the table starts to shrink, and after severalhours the record count is zero. This isn't happening to the other twotables, and I can't figure out why.If you have no idea what might be causing this, perhaps you can suggestsome places to start looking. This is Win2k SP4 with SQL 2000 SP3.Thanks much.
View 1 Replies
View Related
Apr 11, 2007
We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.
View 13 Replies
View Related
Dec 4, 2007
Hi,
What transformations can be used to generate sequence numbers in a data flow?
View 2 Replies
View Related
Aug 22, 2006
We have project in SSIS:
Control Flow
Load Data Flow Task to Copy Data Flow Task to Scrip Task
Data Flow under
Load Data Flow Task has
Flat File Source to Row Count1 to OLE DB Destination (ODS database)
Variable name for Row Count1 is RowCount
Data Flow under
Copy Data Flow Task has
OLE DB Source to RowCount2 to OLE DB Destination (WIMS database)
Variable name for Row Count2 is RowNumber
Data Flow under Script Task is code:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim varMyRowCount As Variable = Dts.Variables("RowCount")
Dim varMyRowNumber As Variable = Dts.Variables("RowNumber")
Dim varPackageName As Variable = Dts.Variables("PackageName")
Dim varStartTime As Variable = Dts.Variables("StartTime")
Dim varInstanceID As Variable = Dts.Variables("ExecutionInstanceGUID")
Dim varMailMsgtext As Variable = Dts.Variables("MailMsgText")
Dim PackageDuration As Long
Dim Filenum As Integer
Dim FilNam As String
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' Event log needs
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim sSource As String
Dim sLog As String
Dim sEventMessage As String
Dim sMachine As String
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
PackageDuration = DateDiff("s", varStartTime.Value, Now())
sSource = "RowCountReporting from SSIS"
' We need the message posted to the Application event log.
sLog = "Application"
sEventMessage = "Flat File Row Process: " & Chr(10) _
& " case Rows:" + varMyRowCount.Value().ToString + Chr(10) _
& "Wims Rows Processed: " & Chr(10) _
& " case Rows:" + varMyRowNumber.Value().ToString + Chr(10) _
& "Started: " & varStartTime.Value().ToString _
& Chr(10) _
& "Current Time:" & System.DateTime.Now _
& Chr(10) _
& "=============================================" _
& Chr(10) _
& "Package Run Duration in seconds: " & PackageDuration _
& Chr(10) _
& "Execution GUID: " & varInstanceID.Value().ToString
sMachine = "."
varMailMsgtext.Value = sEventMessage
If Not EventLog.SourceExists(sSource, sMachine) Then
EventLog.CreateEventSource(sSource, sLog, sMachine)
End If
Dim ELog As New EventLog(sLog, sMachine, sSource)
ELog.WriteEntry(sEventMessage, EventLogEntryType.Information, 777, 2)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
We want to verify flat file count should be same as the data load to WIMS database.
Now if I run this job I get the error message:
Error: Failed to lock variable "RowCount" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
My co-work said even I get balance from RowCount1 and Rowcount2 still need to have query from table in Wims database because for SQL server sometimes process counts not same as the data results load in table so need select count(*) from table. How and where I can put at the job.
My design is row count from flat file put one variable then row count from final table put the other variable then send e-mail to me show both variable for testing.
For real job I need if both variables same ok else send me an e-mail.
Any one has any idea? I will very appreciate.
Thanks.
Grace
View 1 Replies
View Related
May 6, 2014
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
View 9 Replies
View Related
Mar 16, 2014
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE
[Code] .....
View 8 Replies
View Related
Oct 24, 2007
I just opened a large table with about 800 columns and 300,000 rows.
Doing the right click open on table displays message: Exception has been thrown by the target of an invocation.
Please help me determin what the error is and how to solve it.
I have google it for days now and no one has similar situation as mine. Many have same error, but their fix is not relevant to my issue. If you know about some SQL query limit please let me know.
Funny thing is that if I right click table and do script ---> select, then it does pull the data. ONly doesn't work when I do "Open Table."
Thanks for your help.
View 1 Replies
View Related
Sep 21, 2007
Hello,
I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly.
How would someone update a table with data which is calculated from that same table.
This is a made up example. Lets say I have a table:
CREATE TABLE [dbo].[tst_source](
[tst_key] [int] IDENTITY(1,1) NOT NULL,
[object] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color_count] [int] NULL
)
I would like to populate the field color_count with the count of items listed with that color.
Data:
tst_key object color color_count
1, chair blue NULL
2, table black NULL
3 lamp black NULL
----
So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two.
tst_key object color color_count
1, chair blue 1
2, table black 2
3 lamp black 2
-- With T-SQL I would use the following code:
update tst_source
set color_count = res.cnt
from tst_source
Inner join (select count(tst_key) cnt,color
from tst_source
group by color) res
ON res.color = tst_source.color
But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use?
Thank you.
View 11 Replies
View Related
Apr 25, 2015
Regarding Snapshot replication....
If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....
Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?
View 2 Replies
View Related
Nov 8, 2015
I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table.  It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions.Â
If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance?Â
Below is the error message:
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).
The statement has been terminated.
DDL of the target table:
CREATE
 TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,
[Code] ....
View 8 Replies
View Related
Dec 6, 2007
I am having a problem on updating data in DB2 target table.
I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source.
I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link.
Trail 1real requirement):
When I used the SQL query:
delete from table where Col1=? and Col2=?
I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to.
Trail 2:
When I hard code the parameters :
delete from table1 where Col1='abc' and Col2='xyz'
no parameters will come up, so no mapping. So when I execute the mapping I get the following error:
Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Lookup on above error codes show those are related more to target Db2 database.
I am sure some one might have used the OLEDB command task, not only just insert task.
Any help is really appreciated.
View 14 Replies
View Related
Apr 30, 2015
I am trying to multiple update records in Table B from a single record in Table A. To identify the records that need to be updated I used this:
Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,
Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'
fromTable1
JoinTable3
CRonTable1.istudentid=CR.istudentid
JoinTable2
onCR.icontactid=Table2.iContactID
whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
View 2 Replies
View Related
Feb 10, 2015
I have a table:
declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)
A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).
I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.
If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:
merge ( select * from tableName where id = 10 ) as target
using ...
and I can't have:
merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...
This means I cannot filter the set of rows in the target table to a handful of records where id = 10.
View 1 Replies
View Related
