Help Needed With Insert Statement

Nov 13, 2007

Hi,

  I am trying to insert the follows rows to my production database... and this the sample data

  Row Plan PART_ID FUND_ID TOT_ACT1 TOT_ACT2
Number Num
1170925 129602759 19765P471 BB4928.47 CT0.00 DV26.30 GL153.75 TF0.00 WD0.00 OT0.00 EB5108.52 205.0110 24.04 206.0720 24.79
2170925 129602759 35472P406 BB2663.64 CT325.00 DV87.46 GL26.42 TF530.92 WD0.00 OT0.00 EB3633.44 189.0450 14.09 254.6210 14.27
3170925 129602759 LOAN BB1506.88 CT0.00 DV25.48 GL0.00 TF-530.92 WD0.00 OT0.00 EB1001.44 1506.88 1.00 1001.44 1.00
4170925 148603737 19765L587 BB25.14 CT0.00 DV0.46 GL-0.45 TF0.00 WD0.00 OT0.00 EB25.15 5.3830 4.67 5.4790 4.59
5170925 148603737 19765P471 BB7.48 CT0.00 DV0.05 GL0.23 TF0.00 WD0.00 OT0.00 EB7.76 0.3110 24.04 0.3130 24.79
6170925 148603737 35472P208 BB12.53 CT0.00 DV0.28 GL0.09 TF0.00 WD0.00 OT0.00 EB12.90 0.9360 13.39 0.9570 13.48
7170925 148603737 35472P604 BB7.48 CT0.00 DV0.24 GL0.15 TF0.00 WD0.00 OT0.00 EB7.87 0.4720 15.85 0.4870 16.16
8170925 148603737 315805549 BB29.72 CT0.00 DV0.00 GL2.15 TF0.00 WD0.00 OT0.00 EB31.87 1.5320 19.40 1.5320 20.80
9170925 148603737 197199102 BB5.00 CT0.00 DV0.06 GL0.27 TF0.00 WD0.00 OT0.00 EB5.33 0.1650 30.32 0.1670 31.94

 So the number of rows in this table is 1007 right now my insert query inserts all the data but excepts LOAN  and i want Loans inserted in a seperate column in my production dataabse but thats not happening so can some one pls take a look at this query and see whats wrong... My query is as follows1 INSERT INTO Statements..ParticipantPlanFundBalances1
2 (
3 PlanId,
4 ParticipantId,
5 PeriodId,
6 FundId,
7 Loans,
8 --PortfolioId,
9 Act1,
10 TotAct1,
11 Act2,
12 TotAct2,
13 Act3,
14 TotAct3,
15 Act4,
16 TotAct4,
17 Act5,
18 TotAct5,
19 Act6,
20 TotAct6,
21 Act7,
22 TotAct7,
23 Act8,
24 TotAct8,
25 Act9,
26 TotAct9,
27 Act10,
28 TotAct10,
29 Act11,
30 TotAct11,
31 Act12,
32 TotAct12,
33 Act13,
34 TotAct13,
35 Act14,
36 TotAct14,
37 Act15,
38 TotAct15,
39 Act16,
40 TotAct16,
41 Act17,
42 TotAct17,
43 Act18,
44 TotAct18,
45 Act19,
46 TotAct19,
47 Act20,
48 TotAct20,
49 OpeningUnits,
50 OPricePerUnit,
51 ClosingUnits,
52 CPricePerUnit,
53 AllocationPercent
54 )
55 SELECT
56 cp.PlanId,
57 p.ParticipantId,
58 @PeriodId,
59 Case When a.FUND_ID <> 'LOAN' Then f.FundId ELSE 0 END,
60 CASE When a.FUND_ID = 'LOAN' Then 'LOAN' END as Loanfunds,
61 --planinfo.PortfolioId,
62 Case WHEN a.ACT_ID1 = 'BB' Then 1 END,
63 a.TOT_ACT1,
64 Case WHEN a.ACT_ID2 = 'CT'Then 2 END,
65 a.TOT_ACT2,
66 CASE When a.ACT_ID3 = 'DV' then 3 END,
67 a.TOT_ACT3,
68 CASE When a.ACT_ID4 = 'GL' Then 4 End,
69 a.TOT_ACT4,
70 CAse When a.ACT_ID5 = 'TF' THEN 5 END,
71 a.TOT_ACT5,
72 CASE When a.ACT_ID6 = 'WD' THEN 6 END,
73 a.TOT_ACT6,
74 CASE WHEN a.ACT_ID7 = 'OT' THEN 7 END,
75 a.TOT_ACT7,
76 CASE When a.ACT_ID8 = 'EB' THEN 8 END,
77 a.TOT_ACT8,
78 a.ACT_ID9,
79 a.TOT_ACT9,
80 a.ACT_ID10,
81 a.TOT_ACT10,
82 a.ACT_ID11,
83 a.TOT_ACT11,
84 a.ACT_ID12,
85 a.TOT_ACT12,
86 a.ACT_ID13,
87 a.TOT_ACT13,
88 a.ACT_ID14,
89 a.TOT_ACT14,
90 a.ACT_ID15,
91 a.TOT_ACT15,
92 a.ACT_ID16,
93 a.TOT_ACT16,
94 a.ACT_ID17,
95 a.TOT_ACT17,
96 a.ACT_ID18,
97 a.TOT_ACT18,
98 a.ACT_ID19,
99 a.TOT_ACT19,
100 a.ACT_ID20,
101 a.TOT_ACT20,
102 a.UNIT_OP,
103 a.PRICE_OP,
104 a.UNIT_CL,
105 a.PRICE_CL,
106 IsNull(i.ALLOC_PER1,'0.00')
107 FROM
108 ASDBF a
109
110 --Derive the unique Plan Id
111 INNER JOIN Statements..ClientPlan cp
112 ONa.PLAN_NUM = cp.ClientPlanId
113 AND
114 cp.ClientId = @ClientId
115 --Derive the unique ParticipantId from the Participant table
116 INNER JOIN Statements..Participant p
117 ONa.PART_ID = p.PartId
118 -- Derive the unique fund id from the Fund Table
119 INNER JOIN Statements..Fund f
120 ONa.FUND_ID = f.Cusip
121 OR
122 a.FUND_ID = f.Ticker
123 OR
124 a.FUND_ID = f.ClientFundId
125 Left Outer JOIN INVSRC i
126 ONa.FUND_ID = i.INV_ID
127 AND
128 a.PLAN_NUM = i.Plan_Number
129 AND
130 a.PART_ID = i.PART_ID
131 --Get the unique portfolio name ffor the PArticipant Funds..
132 WHERE
133 --Ignore rows that failed the scrub.
134 a.Import = 1
135 AND
136 --Import only those that are not already in the ParticipantPlanFundBalances table
137 NOT EXISTS (
138 SELECT *
139 FROM
140 Statements..ParticipantPlanFundBalances1 pfb
141 WHERE
142 pfb.PlanId = cp.PlanId
143 AND
144 pfb.ParticipantId = p.ParticipantId
145 AND
146 pfb.PeriodId = @PeriodId
147 AND
148 pfb.FundId = f.FundId
149 )


 

 

any help is appreciated.

Regards

Karen

View 7 Replies


ADVERTISEMENT

(urgent) Help Needed For This Insert Statement

Nov 6, 2007

Hi,
   I am trying to insert the Source name and clientId to a table called clientSource...
 
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields
PlanNumber, Name,                   SRC1Name,           SRC2Name,                       SRC3Name....      SRC20Name
170234         Constructions         Employee Deferral   Employer Discretionary      Employer Matching....
 
And in another table called SourceDBF i have the following fields with data
PlanNumber             PARTID   SOURCE_NUM    etc...
170234              123456789         1
170234              123456789         3
170234              451231334         1
So how do i match the Source_NUM with SRCnames when i insert it into the table..
INSERT INTO Statement..ClientSource(@ClientId,                SourceName)SELECT                   s.SOURCE_NUM FROM SourceDBF ..
 but i am stuck..
any help will be appreciated.
Regards
Karen

View 7 Replies View Related

SQL Statement Needed!!

May 20, 2006

Hi.. I need help with this SQL statement. I don't know if this can be done with a single SQL statement or not!
I have two tables:

tblProducts: ProductID, ProductName
tblOrders: OrderID, ProductID
I need to "SELECT tblProducts.ProductName FROM tblProducts WHERE (tblProducts.ProductID is not in tblOrders Table)".
Thanks in advance...

View 2 Replies View Related

Select Statement Help Needed

Aug 8, 2007

Hi,
Table A
member_no   - character 5course1         - character 1course2         - character 1
Table Bcourse_no      - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value?
Thanks in advance for any assistance,Stewart
 
 

View 2 Replies View Related

Help Needed With This Case Statement..

Mar 4, 2008

Hi,
  In my database i have a column Called EntryDates which has 'Jan1' etc.. stored in it. and i am getting an Xml object and in the object i am getting @EntryDAtesMatch and @EntryDatesPS and they have different months stored in it.
So i want to insert into Entrydates or set the Entrydates as to whats been passed in following scenarios
If @Entrydates is Not Null then i want @EntryDates to be set as @EntryDates.
Suppose @EntryDates is NULL But @EntryDatesMatch OR @entryDAtesPS are not null then i want @EntryDates to be = @EntryDatesMatch..
 
Suppose is @EntryDates @EntryDatesMatch and null i want @EntryDates to be set as @EntryDatesPS...
This is what ihave  tried doing.. but doesnt work as the way i want it..
 
  SET @EntryDates =
CASE --When @EntryDatesMatch = NULL Then @EntryDates
When @EntryDates = NULL and @EntryDatesMatch = NULL Then @EntryDatesPS
When @EntryDates = NULL and @EntryDatesPS = NULL then @EntryDatesMatch
End
 Any help will be appreciated..
Regards,
Karen
 

View 6 Replies View Related

Help Needed In Select Statement

Mar 4, 2008

Hi,
      I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all.
Here is what I have started.
 
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10
This doesn't seem to work. What am I doing wrong here?

View 7 Replies View Related

Sql Select Statement Help Needed.

May 7, 2004

I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.

I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'.
The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.

The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.

sub Page_Load(sender as Object, e as EventArgs)

'collecting the user identity
Dim user as string
user=request("User.Identity.Name")

'running a query to establish what account they have access to all from users table
Dim accountrequesting as string
accountrequesting = "select account_id from users where username = " & user & "'"

'opening the data source and connection
Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx"
Dim Myconn as New SQLConnection(connectionstring)

'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings

Dim MySQL as string ="select prodshipped, account_id from orders"

'produces output
Dim ds as Dataset=New Dataset()

Dim Cmd as new SQLDataAdapter (MySQL, Myconn)


Cmd.Fill (ds,"orders")

MyDataGrid.datasource=ds.tables("orders").defaultview

MyDataGrid.Databind()

End Sub

View 9 Replies View Related

Help Needed With SQL UPDATE Statement

Jan 13, 2006

I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement......
strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam"
Thanks!
Eugh

View 10 Replies View Related

Dynamic Statement Needed?

Apr 23, 2014

I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:

select *
from Table
where D10 > midDaypct * 2 and DOW6 > 0
union
select *
from Table
where DOW6 >midDOWpct * 2 and D10 > 0

We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.

View 5 Replies View Related

Case Statement Help Needed

Aug 14, 2007

Hi,
here is what i am trying to do, when @call=2, then use table1.call
else use table2.call
and get syntax error no matter what I try,

case @services=2 then table1.call else table2.call end

View 3 Replies View Related

SQL Select Statement Needed

Aug 16, 2006

I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian

View 3 Replies View Related

How To Write A Sql Statement To Get The First Needed Row?

May 9, 2007

I have a table with two columns. empnum and empname. there are some data with same empnum but different empname. I just want to get the first empname for the same empnum. Is there a way to write a sql stetment to do this?



Thanks

View 3 Replies View Related

Update Statement Help Needed

Nov 28, 2007

Below is my table structure:


CREATE TABLE [reports].[MDC_DRG](

[InsDrgMdc] [nvarchar](50) NULL,

[InsDrgMdcDesc] [nvarchar](50) NULL,

[MDC] [nvarchar](100) NULL,

[Drg] [nvarchar](50) NULL,

[DrgDesc] [nvarchar](255) NULL,

[AnyDx] [nvarchar](100) NULL,

[Cases] [decimal](18, 0) NULL,

[IntOOS] [int] NOT NULL,

[ChronicOther] [nvarchar](50) NULL

) ON [PRIMARY]

Here is a sample select from the AnyDx field:

AnyDx
,85225,85225,5304 ,5181 ,80501,E8190,3485,
,1983 ,1983 ,49121,1625 ,34550,79381,78079,30001,V1,
,2396 ,2396 ,78039,311,
,33390,33390,31401,49390,
,3410,3410,2768,3051,72293,V146,V4579,V1041,
,43491,43491,25000,4019 ,V1581,
,43491,43491,25000,4919 ,3569 ,4019 ,72252,78650,71,
,43491,43491,2768,30590,34290,4019,V146,7845,2724,2,
,43491,43491,34290,40290,25000,7843,7872,41401,4278,
,43491,43491,412 ,25000,41401,4019 ,2724 ,29680,V1,


I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other".


where AnyDx between '25000' and '25091'

or AnyDx between '41400' and '41406'

or AnyDx between '42800' and '42890'

or AnyDx between '40100' and '40291'

or AnyDx between '34200' and '34292'

or AnyDx between '14000' and '19910'

or AnyDx between '49600' and '49600'

or AnyDx between '27240' and '27240')


My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements?

example:
Update reports.MDC_DRG
SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other'
etc., etc.....

View 12 Replies View Related

CASE Statement Help Needed

Jan 3, 2008

I want to Add in a Case Statement to my Stored Procedure to Identify 5 classifications:

A: Birth to 6 yrs old
B: Female 7 yrs to 18 yrs
C: Male 7 yrs to 18 yrs
D: Female over 18 yrs old
E: Male over 18 yrs old

So Im looking for something like this:

CASE
WHEN [Patient Age] >= 6 THEN 'A'
WHEN [Patient Age] between .....

Any help is appreciated!

Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM Appointments a
WHERE a.AppointmentsId = @AppointmentsId

SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
'291.PatLast'=IsNull(pp.Last,''),
'292.PatFirst'=IsNull(pp.First,''),
'293.PatMiddle'=IsNull(pp.Middle,''),
'294.PatientAddr1'=IsNull(pp.Address1,''),
'295.PatientAddr2'=IsNull(pp.Address2,''),
'296.PatientCity'=IsNull(pp.City,''),
'297.PatientState'=IsNull(pp.State,''),
'298.PatientZip'=IsNull(pp.Zip,''),
'299.PatientCountry' = ISNULL(pp.Country,''),
'300.PatientBirthdate' = pp.Birthdate,
'301.PatientSex'=IsNull(pp.Sex,''),
'302.PatientPhone1'=IsNull(pp.Phone1,''),
'303.PatientSSN'=IsNull(pp.SSN,''),
'304.PatOccupation'=IsNull(pp.EmpOccup,''),
'305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
'306.PatBudget'=IsNull(g.Budget,0),
'307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
'308.PatSuffix'=IsNull(pp.Suffix,''),
'400.PatientAge' = CASE WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
FROM PatientProfile pp
LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE pp.PatientProfileID = @PatientProfileId

View 3 Replies View Related

Missing A Needed Using Statement But Not Sure Which One

Mar 31, 2006

I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing?

TIA

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

View 4 Replies View Related

Help Needed With Simple Case Statement In SQL

Mar 1, 2007

Hello,
 
I am looking to modify this Case Statement.  Where it says ELSE '' I need it to display the actual contents of the cell.  1 = Yes , 0 = No, (any other integer) = actual value. 
Right now if the value is anything other than 1 or 0,  it will leave the cell blank.
CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus
Thank You.

View 1 Replies View Related

Help Needed Creating Select Statement

Mar 20, 2007

Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!

View 2 Replies View Related

Help Needed With Select Statement Using Calculation

May 2, 2008

Hi,

Any help with this would be great. I have SQL tables all in the following format:

ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE

Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g

(326-229) + 278 = new measure_value.

This is for each id-product where the following 3 conditions meet:

the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.

thanks

Jay

p.s this what i have started with at the moment as a test however it dont work.

SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)

View 3 Replies View Related

CASE Statement Help Needed - SQL 2005

Jan 29, 2008

In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:

(select top 1 visit from patientvisit pv
where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]

My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:

Case
When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2'
When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more'
else 'No detected visit'
END

So basically, either your in 2 yrs or your not.

My Current Query:





/* Patient List*/
SET NOCOUNT ON

DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)

SELECT
PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0),
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City,
PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'),
Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate,
Status = ml1.Description,
pp.BirthDate,
(select top 1 visit
from patientvisit pv
where visit >= ISNULL(NULL,'1/1/1900') and
visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID
and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]

FROM PatientProfile pp
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId

WHERE etc ......

View 1 Replies View Related

Syntax Check Needed On Case Statement

Feb 26, 2008

Hello experts!

I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....





Code Snippet

WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)


Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.


Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.


Any help is appreciated.

View 3 Replies View Related

Help Needed With INSERT Trigger

Nov 1, 2007

Hello,
It is rare that I have a user for Triggers, but I have a special case. In a table named tbl_PaymhistCurrentWeek, I have a column called 'AcctCode'. This column will contain data that will be derived from another column called AcctCodeWithZeros. AcctCodeWithZeros contains an account code with leading zeros. The column AcctCode needs to contain the value in AcctCodeWithZeros, only without the leading zeros.

I tried to set up something in a Derived Column Transformation within SSIS, but I can't use PATINDEX.

Here is the latest version of the Trigger:

*****************
CREATE TRIGGER [dbo].[tr_AcctCode]
ON [dbo].[tbl_PaymhistCurrentWeek]
FOR INSERT
AS

BEGIN
INSERT tbl_PaymhistCurrentWeek
(AcctCode, Co, AccountNoWithZeros)
SELECT
id.SUBSTRING([AccountNoWithZeros], PATINDEX('%[^0]%', [AccountNoWithZeros]), 14)AS AcctCode, id.Co, id.AccountNoWithZeros
FROM Inserted id
END
****************
The default for AcctCode is an empty string, which is all I get when I insert into the table. AcctCode will be part of a primary key, so I don't think I can set up AcctCode to be a calculated column. What am I doing wrong?

The DDL for the table is as follows:
tbl_PaymhistCurrentWeek
( AcctCode varchar(10) Default ' ' Not Null,
Co char(2) Not Null,
AcctCodeWithZeros varchar(20) Null
CONSTRAINT (PK_PaymhistCurrentWeek) Primary Key Clustered
(AcctCode, Co)
)


Thank you for your help!

cdun2

View 8 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage

Apr 21, 2008

An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View 7 Replies View Related

Crazy SQL Statement Help Needed... Converting The Rows To Columns (sort Of)...

Sep 19, 2006

This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName:    courseName:      completeDate:Jane Doe      Welding              2/2/2002Jane Doe      Brain Surgery      3/7/2005Alton Brown  Scuba Diving       9/23/2004Alton Brown  Welding              11/4/2004John Doe      Brain Surgery      6/14/2003End result of select statement:                        Welding                Brain Surgery             Scuba DivingJane Doe         2/2/2002                   3/7/2005                     Alton Brown    11/4/2004                                                  9/23/2004John Doe                                          6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.

View 8 Replies View Related

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement

May 15, 2008

Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.


ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END



ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View 14 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement

Jan 14, 2008


This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1


/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id bigint )

declare @forename VARCHAR(100)
declare @surname VARCHAR(50)

set @forename = N'john'
set @surname = N'smith'

INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View 5 Replies View Related

Script Needed To Insert Data To A Different Dbase

Oct 12, 2000

Hello,

I need a sql server 7 script that will migrate data from
my DEV dbase to my WEB dbase. The data desired is for the field
rig_id for rig number 0018. The shell of both dbases are similar.
The table is called rig. An snapshot is below:

DBASE name - DEV

TABLE name - rig

customer rig_id amount
Williams 0011 328
Borsha 0013 256
Kaila 0018 411
Yoida 0021 347

The rig table also exists on the WEB dbase. However, the Kaila data
for rig_id number 0018 needs to be added to the WEB dbase's rig table.
The Williams, Borsha, and Yoida data already exists on both tables.

I need to perform this migration w/ a TRANSACT-SQL stored procedure.

Thanks Very Much IN Advance,

Dave
gunnardl@yahoo.om

View 1 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Urgent Help Needed: How To INSERT Into A Field Having UniqueIdentifier Data Type

Jan 8, 2008

I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to  If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.

View 5 Replies View Related

Insert Statement Which Uses A Return Value From An SP As An Insert Value

Jul 20, 2005

I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)

View 3 Replies View Related

INSERT Statement

May 1, 2007

I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word 

View 3 Replies View Related







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