INSERT Based On Conditional

Jun 4, 2008

Good Afternoon,

I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!

Here is the problem I am tackling:

I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.

I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.

The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:

1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.

2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.

3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).

My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).

If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!

Thanks,
Bryan

View 9 Replies


ADVERTISEMENT

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

View 6 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?



$3.99/yr .COM!
http://www.greatdomains4less.com

View 3 Replies View Related

Conditional Sum Based On Count

Oct 15, 2013

In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)

In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.

Basically, I would like to implement logic that would work like this:

SELECT Date, Store#, Item#,
CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales)
ELSE %Total Sales
END

FROM (some tables and joins)
GROUP BY Date, Store#, Item#

However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).

View 5 Replies View Related

Conditional Sum Based On Visibility

Mar 12, 2007

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

View 9 Replies View Related

Inner Join Based On Conditional Column

Mar 5, 2014

I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....

Sales Table:

BillMasterId BillDate CustomerId NetAmount CreditAmount

26 03/03/2014 101 1000 1000

My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101

My Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0

Exact Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0

View 7 Replies View Related

Conditional Split Based On Conditions

Sep 23, 2007

Hi,

I have the following table in MsAccess


EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?


Thanks,

lolsron

View 6 Replies View Related

Conditional Formatting Based On Second Dataset

Jun 15, 2007

I am trying to create a report which has conditional formatting.



The primary dataset is a view of objects with several values



eg



object1,0,4,0,1

object2,0,3,1,1



The secondary dataset is the comparison table and just contains the values

eg



0,3,1,1



I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get



Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.



Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?



Ideas gratefully received !

View 7 Replies View Related

Conditional Formating Based On Second Dataset.

Mar 15, 2007

I have two databases located on seperate machines, using different platforms and credentials.

I have setup dataset1 source to the first database wich provides me the following colums

Incident_id, description, date, status.

And dataset2 datasource provides

incident_id, is_billable

My report is a tabular report showing the four fields from dataset1.

I am trying to apply some conditional formating wich will link the two datasets on the incident_id field.

something along the line of if the dataset1.incident_id is in dataset2.incident_id then turn it red.

I tried using the below expression on the color property but the IN part is not allowed.

=iif(Fields!INCIDENT_ID.Value,"Dataset1" in Fields!INCIDENT_ID.Value,"Dataset2","Black","Red")

View 2 Replies View Related

Conditional Fields Based On Export Type

Aug 27, 2007

I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.

Any help would be appreciated.

Jerry

View 2 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Retrieving Data From A DB Based On Output Of A Conditional Split

Sep 5, 2007



This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.

Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.

Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.

How can I do this?

Thank you!

Nolan

View 1 Replies View Related

Analysis :: Conditional Sum Based On Latest Survey Answer

Jul 24, 2015

Basically our cube contains two different Facts:

- Sales Volume: Information about sold articles to a customer incl. Selling date
- Survey: irregular answered survey questions about customers incl. date of answer
and three Dimensions:
- Customer
- Date
- Survey Answer: Information about possible Answer values (e.g. Yes / No)

Relations:
Fact: Sales Volume  ------>  Dim: Customer  <------  Fact: Survey   ------>  Dim: Survey Answer
                    '-------------->      Dim: Date       <----------------'

We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.

For example:
Selected Time period: Jan - Jul 2015
Sales Volume Customer X - Jan - Jul 2015: 1000 Litres
Sales Volume Customer Y - Jan - Jul 2015: 500 Litres

Surveys answered:

15th Jan 15: Customer X, Survey Question A: Yes
2nd Mar 15: Customer X, Survey Question A: No
20th Apr 15: Customer X, Survey Question A: Yes

10th Feb 15: Customer Y, Survey Question A: Yes
20th Jul 15: Customer Y, Survey Question A: No

Latest survey answer (Jan-Jul) Customer X, Question A: Yes
Latest survey answer (Jan-Jul) Customer Y, Question A: No

Excel Pivot should show something like this:

Question       |      Latest Answer:     Yes                |       No         |

------------------------------------------------------------------------------------
A                    |     1000 Litres     |    500 Litres    |

How this is possible and has to be implemented?

View 5 Replies View Related

Integration Services :: SSIS Execute Multiple Packages Based On Conditional Split

Sep 4, 2015

We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set.  I am getting the below error.

[Execute SQL Task] Error: Executing the query "SELECT  isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating

Aug 2, 2007



I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like


Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19


how can i do following 3 things


1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

View 1 Replies View Related

Conditional Insert

Oct 29, 2006

Hi,

Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into ssotarget:

INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)

SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS

FROM

FULLSOURCE

GROUP BY PIN


Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:

SELECT [PIN]
,[SYSTEM]
,[Header]
,[Property]
,[Value]
,[Status]
,[ID_Header]
,[ID_Property]
FROM [fullsource]

View 1 Replies View Related

Conditional Insert

Oct 22, 2004

I need to do a conditional insert. This is what I have tried, it does not work.
What am I doing incorrectly?

IF (SELECT COUNT(*) FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0

INSERT INTO TBL5
SELECT TBL1.PRO_SITE, TBL1.MODEL_ID,
TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1,
TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL
AS Expr5, 0 AS RAMP
FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC

UPDATE TBL5
SET TEAMS=0
GO
UPDATE TBL5
SET TEAMS = (SELECT NUM_SLOTS
FROM TBL6 R1
WHERE (R1.TEAM_ID = TBL5.TEAM_ID)
)
WHERE (TEAM_ID =
(SELECT TEAM_ID
FROM TBL6 R3
WHERE (R3.TEAM_ID = TBL5.TEAM_ID)))
GO
UPDATE TBL5
SET TOTALTEAMS = TEAMS + RAMP
GO

I need to do a conditional because sometimes the select that returns data contains no records.

Thanks...

View 1 Replies View Related

Conditional INSERT

Aug 8, 2006

Hello all. I am inserting data into a table from a linked server (.XLS file). One of the columns deals with dates. In the .XLS file some ofthe dates are invalid (either wrong format or wrond data). What i am looking for is how to insert only the rows where the dates are correct. My basic structure is:



INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE)
SELECT CAST(EMPLOY_REF AS VARCHAR(10)),
CAST(CONTRIB_CHANGE AS DATETIME)
FROM mockdownload_dd...[Pensions$]


thanks people

View 8 Replies View Related

Conditional Insert Trigger

Mar 14, 2006

Is there any way to create a "Conditional Insert Trigger"
My Scenario is this;
When a user adds an email address to the database, I want to look to see if the email address is like '%@acme-holdings%' and if it is then to change the value to 'Not allowed', otherwise to leave it alone and go ahead with inserting the original email address

TIA

ICW

View 9 Replies View Related

Conditional Column Name On Insert

Jan 21, 2006

I need to dynamic select a column in which insert a vale based on aparameter value, I have this code, but it throws an incorrect syntaxerror.How do I dinamically select a column to insert based on a parameter?Create PROCEDURE dbo.UpdateDetalleOT (@eotId int,)insert into OT (select Casewhen @eotId = 1 THEN OTFechaBorradorwhen @eotId = 2 THEN OTFechaAAsignarend) values ....Best RegardsFabio Cavassinihttp://www.pldsa.com

View 9 Replies View Related

Conditional Insert Without Using Exec

Dec 27, 2007

I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.





Code Block

CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO

IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')

INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')

This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.

Any help would be appreciated

View 3 Replies View Related

Conditional Bulk Insert

Sep 19, 2007



Hi

I am doing bulk insert as follows. The @lastUpdate, @filePath, @formatFile comes as a parametes to stored proc

INSERT INTO Categories
SELECT CategoryId, @LastUpdate FROM OPENROWSET
(

BULK @filePath ,
FORMATFILE = @formatFile,
FIRSTROW =2
)
AS a
This works fine for me.

But my new requirement is that i shouldn't insert the CategoryId if it exists

How can we have conditional bulk insert? i am using Bulk insert as the file might have millions of category Ids.

Please provide your inputs that executes much faster

Best Regards,
~Mohan Babu

View 1 Replies View Related

Conditional Insert In SQL Server 2000?

Oct 17, 2005

I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...I have a table called customers and a table called lockscustomers has 3 fields CustomerID, FirstName, and LastNamelocks has fields LockID, CustomerID, UserID, SessionID, and TimeStampI need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.Any ideas or comments?

View 1 Replies View Related

Best Practice For Conditional Insert Else Select?

Aug 7, 2007

I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.

So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);


I see two main approaches for how I can do my get-id-with-insert-if-needed.

(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN

INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END

(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)

SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE

SET @id = SCOPE_IDENTITY();

From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.

Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?

View 1 Replies View Related

Bulk Insert + Conditional Split

Mar 12, 2008



Hello all,

I just wanted to know whether is it possible to use Bulk Insert and Conditional Split together for one transformation.

Regards,
Kapadia Shalin P.

View 3 Replies View Related

T-SQL (SS2K8) :: Selection With Conditional Insert Union

Apr 4, 2014

In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results

--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))

[Code] ....

---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment

--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'

--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'

--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union

[Code] .....

View 3 Replies View Related

Conditional Split For Insert Or Update Cause Dead Lock On Database Level

Aug 28, 2007

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

View 7 Replies View Related

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

Feb 15, 2007

Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;

DBPROP rgProps[1];
DBPROPSET PropSet[1];

rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}

View 6 Replies View Related

Insert Based On Query

Nov 2, 2004

Hi everyone -

I have a simple query that will return a result set that
has a unique value in it (primary key) -

I would like to cycle through each of these values
returned from the query, and create a new row
in another table.

I do not want to use a cursor -


The first query is pretty simple....

select p_id from table1 where active=0

the query i need to execute for each of the p_id that
is returned in the first example is....

insert into table2 (p_id_field, text_field) values (@p_id, 'text')

the @p_id is filled in from the primary query...

please advise


thanks
tony

View 1 Replies View Related

Conditional Subscription / Conditional Execution Of Report

Mar 7, 2008



Hello everyone,

Is there a way in order to execute a subscribed report based on a certain criteria?

For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.

My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.


Any help or suggestions will be much appreciated.

Thanks,
Larry

View 6 Replies View Related

Insert Based On Query Values

Sep 20, 2002

Using SQL Server 7 w/SP4.

I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.

Example:

SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50

IF @@ROWCOUNT > 0 BEGIN
INSERT INTO tblAudit
(ID, NAME)
VALUES
(@ID, @NAME)
END

Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.

Thanks for any help.

View 3 Replies View Related

Insert Based On A Select Query

Sep 20, 2005

Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren

View 4 Replies View Related







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