Can I delete the record if it exist before we do an insert at the DataFlow level base on a key of the record we are working on? Basically we want to keep history records and delete and reinsert any records that exist in the table.
View 5 RepliesHi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!
View 6 Replies View RelatedDear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
help on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B
Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist
select
empID,ShiftDate,shiftType
from
table_A
table_A
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2
TABLE B
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
TNX for the help
i use a single stored procedure to update many tables in sql server 2014 database, using defalut transaction isolation level we got random performance issues.
maybe it would better to use read uncommitted isolation level?
what's happens ,in the both cases( read committed, uncommitted) if the sp is called at the same time passing the same @key parameter?
this is a sample to show of the real stored procedure works:
CREATE PROCEDURE SP_Test
@Key int,
@Values1 Values1 readonly,
@Values2 Values2 readonly,
@Values3 Values3 readonly
[code]....
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.?
View 20 Replies View RelatedIs that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View Related
hi All,
Following code generates error 107 (SQL Server 2000, SP3a):
SELECT
*
FROM
dbo.AssessmentItemHierarchy Parent
INNER JOIN dbo.RaterCategory
INNER JOIN dbo.RaterType
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN dbo.AssessmentRater
ON AssessmentRater.RaterTypeID = RaterType.ID
INNER JOIN dbo.AssessmentItem
ON AssessmentItem.ID = Parent.ChildItemID
INNER JOIN dbo.Assessment
ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN dbo.AssessmentResponse
ON AssessmentResponse.AssessmentItemID = AssessmentItem.ID
ON AssessmentResponse.AssessmentRaterID = AssessmentRater.ID
INNER JOIN dbo.ImageInstance
ON ImageInstance.ID = RaterType.ImageInstanceID
INNER JOIN dbo.AssessmentItem ParentGUID
ON Parent.ParentItemID = ParentGUID.ID
Error on Run:
Msg 107, Level 16, State 2, Line 2
The column prefix 'Parent' does not match with a table name or alias name used in the query.
The only work-around I have found is a rewrite of order:
SELECT
*
FROM
AssessmentItemHierarchy Parent
INNER JOIN RaterCategory
INNER JOIN RaterType
INNER JOIN AssessmentRater
INNER JOIN AssessmentItem
INNER JOIN Assessment ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN AssessmentResponse ON AssessmentItem.ID = AssessmentResponse.AssessmentItemID
ON AssessmentRater.ID = AssessmentResponse.AssessmentRaterID
ON RaterType.ID = AssessmentRater.RaterTypeID
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN ImageInstance
ON RaterType.ImageInstanceID = ImageInstance.ID
ON Parent.ChildItemID = AssessmentItem.ID
INNER JOIN AssessmentItem ParentGUID ON Parent.ParentItemID = ParentGUID.ID
Not very pretty... anyone know why I'm getting this error message with the first version of my query?
ACADEMIC_YEAR COURSE_REFERENCE TERM
------------- ---------------- --------
2014 -1465586182 TERM4
2014 -772484485 TERM4
With the above data, user must check and delete the row only if exist
I have query some thing like this:
DELETE FROM epas_publish_course where COURSE_REFERENCE IN ( SELECT * FROM (SELECT COURSE_REFERENCE FROM epas_publish_course WHERE COURSE_REFERENCE) AS X) ;
which deleting the complete table but i need to delete one particular row...
hi,
I would like asked an opinion about the system of my coding if is ok? I have one table here to modify. My point here is to count the record to avoid a duplicate record .
rs.open "Select count (field) as name from table Where field='001'",cn
If rs!name > 0 then
Update sqlstatement
Else
Insert sqlstatement
Endif
Any suggestion pls..
Hi all,
I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like
Procedure [dbo].[CleanResults]
@RecsToKeep bigInt,
@Output Int output
as
Declare @Date Datetime
Declare @Count bigint
set @Date = getdate()
Select @Count = Count(UniqueID) from [Main]
Print 'THE COUNT IS'
print @Count
if ( @Count > @RecsToKeep) begin
set @Count= @Count - @RecsToKeep
Print 'THE Number to delete is'
print @Count
select TOP(@Count) UniqueID from Main order By [Main].[TestDateTime] desc
Delete from [Main] where exists (select TOP(@Count) * from Main order By [Main].[TestDateTime] desc);
set @Output = @Count
end
else begin
set @Output = -1
end
whats odd is that the select staement will evaluate correctly and return the oldest record @Count record, however the delete stament removes all the records. An advice would be appriciated.
Thanks Christopher
PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated
Hello,
I need to delete rows from one table that appear in another table.
For example:
newnames has fname, lname, age
oldnames has fname, lname, age
So, I want to be able to remove the rows from newname where there are rows in oldnames. This should be simple enough.
This is what I'm trying to use but it is deleting everything from oldnames.
delete from oldname
select * from newnames
Hi all,
I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.
The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?
Am I correct in the way I am doing or there is something better to handle this?
Thanks in advance
Can I set up the security so that a user could only see certain records (a filter)?
TIA!
I am writing a VB program, which needs to update record A in one table using transaction control (BeginTrans & CommitTrans). But due to the page-level locking, before the "CommitTrans", other users couldn't not read other records in the same table.
Is there any way that I can do the record/row level locking? If possible, could you provide me some VB source code? Such as how to use "DBCC ROWLOCK" or how to set for row-level locking.
Thanks for your kind help.
Hi All,
Is there a checkpoint at record or row level? i mean let say i have 2 million rows and i want to transfer from one db to another, when i start tranfering about 1 million records the package fails, thus next time i run i want to continue from where it was, i.e. since 1 million records were transfered i don't want to repeat them, just like checkpoint at Task Level. Farther information I am working on SSIS.
Thank you,
Sami
Hi all:
for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi");
if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like :
if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database");
I can't imagine I got the bool variable via exception...
thanks to all..
Hello,few users have problems with our application(ADP/MsAcces2000+SqlServer2000). Whenthey want to open a report which source is a stored procedure, they getmessage that "record source specified..... do not exist" - of course,it's not true.More details:- other users don't have this problem- they have WinXP- all users have the same permissions to SQL objects- I've checked Microsoft KB(http://support.microsoft.com/kb/243532/en-us) and they write about"dbo." before source name - I use that, so it's not a solution. Whatmore, in application there are lists which have SP as row source andwithout "dbo." and it works. (In lists a row source is defined as:"exec <sp_name>", in reports as: "dbo.<sp_name>").- computers have all service packs, new version of MDAC, etc.- I asked another user, who doesn't have this problem on his machine,to login on "bad" machine and he couldn't open reports too.Maybe someone could help? because I don't have more ideas.
View 1 Replies View RelatedHi,
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear:
column id (int)
column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
thanks,
Stephane
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View RelatedHi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
View 1 Replies View Relatedhello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
View 3 Replies View RelatedThis function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete
(
@Schema NVARCHAR(128) = NULL,
@Table NVARCHAR(128) = NULL
)
RETURNS@Return TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
AS
BEGIN
DECLARE@Constraints TABLE
(
RowID INT NOT NULL,
Indent SMALLINT NOT NULL,
[Catalog] NVARCHAR(128) NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(128) NOT NULL,
[Column] NVARCHAR(128),
pkCatalog NVARCHAR(128),
pkSchema NVARCHAR(128),
pkTable NVARCHAR(128),
pkColumn NVARCHAR(128),
pkType NVARCHAR(128),
pkSize INT,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL
)
INSERT@Constraints
(
RowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
IsSelfJoin,
HasPk
)
SELECTRowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
SelfJoin,
CASE
WHEN [Column] IS NULL THEN 0
ELSE 1
END
FROMdbo.fnTableTree(@Schema, @Table)
IF @@ROWCOUNT = 0
RETURN
DECLARE@SQL TABLE
(
ID INT IDENTITY(1, 1),
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
DECLARE@Indent SMALLINT,
@RowID INT,
@ID INT,
@TSQL NVARCHAR(4000),
@RowSQL NVARCHAR(4000),
@EndSQL NVARCHAR(4000),
@pkColumn NVARCHAR(128),
@IsSelfJoin TINYINT,
@HasPk TINYINT
DECLARE@Unwind TABLE
(
RowID INT NOT NULL,
StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
[SQL] NVARCHAR(4000)
)
WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
BEGIN
SELECT TOP 1@RowID = c.RowID,
@ID = c.RowID,
@Indent = c.Indent,
@TSQL = N'',
@EndSQL = N'',
@IsSelfJoin = c.IsSelfjoin,
@HasPk = c.HasPk
FROM@Constraints AS c
LEFT JOIN@SQL AS s ON s.RowID = c.RowID
WHEREs.RowID IS NULL
ORDER BYc.Indent DESC,
c.RowID DESC
WHILE @ID > 0
BEGIN
IF @Indent = 0
SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
ELSE
SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
@pkColumn = QUOTENAME(c.pkColumn),
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
SELECT TOP 1@ID = c.RowID,
@Indent = c.Indent,
@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID < @ID
AND c.Indent < @Indent
ORDER BYc.Indent DESC,
c.RowID DESC
IF @@ROWCOUNT = 0
SET@ID = 0
SET@TSQL = @RowSQL + @TSQL
END
INSERT@SQL
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
VALUES(
@RowID,
@IsSelfJoin,
@HasPk,
@TSQL + @EndSQL
)
IF @IsSelfJoin = 1
BEGIN
DECLARE@Yak NVARCHAR(160),
@Catalog NVARCHAR(128),
@Column NVARCHAR(128)
SELECT@Yak = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
@Catalog = [Catalog],
@Schema = [Schema],
@Table = [Table],
@Column = [Column],
@Catalog = [Catalog],
@Table = [Table],
@pkColumn = pkColumn
FROM@Constraints
WHERERowID = @RowID
SET@RowSQL = 'DECLARE@Lvl INT
SET@Lvl = 0
DECLARE@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
BEGIN
SET@Lvl = @Lvl + 1
INSERT@Stage (Lvl, RowKey)
SELECT@Lvl,
t.' + QUOTENAME(@pkColumn) + '
FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
AND s.Lvl = @Lvl - 1
LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
WHEREcr.RowKey IS NULL
END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'
INSERT@Unwind
(
RowID,
[SQL]
)
VALUES(
@RowID,
@RowSQL
)
END
END
INSERT@Return
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
SELECTs.ID,
s.IsSelfJoin,
s.HasPk,
CASE
WHEN u.RowID IS NULL THEN s.[SQL]
ELSE u.[SQL]
END
FROM@SQL AS s
LEFT JOIN@Unwind AS u ON u.RowID = s.RowID
ORDER BYs.ID,
u.StepID
RETURN
ENDE 12°55'05.25"
N 56°04'39.16"
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
Dear Friends,
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
Thanks!!
Hi,we are executing the following query in a stored procedure using snapshot isolation level:DELETE FROM tBackgroundProcessProgressReportFROM tBackgroundProcessProgressReport LEFT OUTER JOIN tBackgroundProcess ON
tBackgroundProcess.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID LEFT
OUTER JOIN tBackgroundProcessProgressReportItem ON
tBackgroundProcessProgressReport.BackgroundProcessProgressReportID =
tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportIDWHERE (tBackgroundProcess.BackgroundProcessID IS NULL) AND (tBackgroundProcessProgressReportItem.BackgroundProcessProgressReportItemID IS NULL)The query should delete records from tBackgroundProcessProgressReport which are not connected with the other two tables.However, for some reasone we get the following exception:System.Data.SqlClient.SqlException:
Snapshot isolation transaction aborted due to update conflict. You
cannot use snapshot isolation to access table 'dbo.tBackgroundProcess'
directly or indirectly in database 'RHSS_PRD_PT_Engine' to update,
delete, or insert the row that has been modified or deleted by another
transaction. Retry the transaction or change the isolation level for
the update/delete statement.The exception specifies that we are
not allowed to update/delete/insert records in tBackgroundProcess, but
the query indeed deletes records from tBackgroundProcessProgressReport,
not from the table in the exception.Is the exception raised because of the join?Has someone encountered this issue before?Thanks,Yani
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...