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 )
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
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...
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
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
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?
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"
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
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.
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
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?
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.....
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?
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.
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!
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)
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
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.
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) )
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.
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
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.
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]
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 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.
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
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 ************************************************/
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
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:
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.
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.
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.
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)
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
