Performing Multiple Inserts In Mssql Sproc
Apr 27, 2005
I am trying to insert a message into my database for every "league" I have in the database. I would like to do this in one sproc but am not sure how this would be done. Here is the loop if it has to be done outside of sql
Code:
message = "This is a test message."
leagues[] = getAllLeaguesInSite()
begin transaction
for each league in leagues
insertLeagueMessage(league, message)
if noErrors
commit transaction
else
rollback transaction
-----
as you can see it would be nice to be able to do this all in mssql . If it can be done please let me know.
Thanks
View 4 Replies
ADVERTISEMENT
Mar 1, 2006
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
View 3 Replies
View Related
May 14, 2006
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE@CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
End
Else
Begin
Set @SV=Cast(@ListStr as varchar)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
Thanks
View 2 Replies
View Related
Jul 14, 2005
Hi, I'm trying to create a form where new names can be added to a database. The webform looks like this:<body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> Name:<asp:TextBox ID="newName" runat="server" /> <INPUT id="NewUserBtn" type="button" value="Create New User" name="NewUserBtn" runat="server" onServerClick="NewBtn_Click"> </form>And the code behind looks like this:Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection("server=databaseserver;database=db;uid=uid;pwd=pwd") MyCommand = New SqlDataAdapter("insert into certifications (name) values ('" & newName.Text & "'); select * from certifications", MyConnection)
DS = New DataSet MyCommand.Fill(DS, "Titles")
Response.Redirect("WebForm1.aspx", True) End SubWhen I try to insert one name it works. When I try to insert a second name, it overwrites the old one. Why is that?Thanks.James
View 3 Replies
View Related
Sep 10, 2007
I'm try to a multiple insert from one database to another by using this code:insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type) (select top 30 acno,surname,name,id,type from [PretoriaDB].[dbo].[WorkingDB]) but I keep on getting this error:Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated. Can any one help!!
View 7 Replies
View Related
Jun 6, 2004
Hi All,
Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.
Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users
INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@@Identity,'SomeOtherValue')
Thanks
Tanveer
View 3 Replies
View Related
Apr 11, 2007
hi I was just writing for some general advice. If I am to do many updates on a table is it okay to keep updating with many insert statements inside of a loop closing the connection each time? Here is some pseudo code to give you an idea:
cnn.open();
cmd = "insert into Cats values (red,4994,homer)";
for(int i=0; i<10000; i++)
{
cmd.executenonquery();
cnn.close();
}
View 2 Replies
View Related
Aug 21, 2006
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI
54952, 1002, Menasha, WI
54952, 1003, Menasha, WI
54952, 1004, Menasha, WI
54952, 1005, Menasha, WI
54952, 1006, Menasha, WI
54952, 1007, Menasha, WI
54952, 1008, Mensaha, WI
54952, 1009, Mensaha, WI
54952, 1010, Mensaha, WI
54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
View 2 Replies
View Related
Nov 12, 2007
I have a ListBox controls that contains about 5 items. A stored procedure is executed based on selections of other controls ont he screen, but I cann't figure out how to properly get the dynamically selected conditions passed to the sproc from C#.
If a user selects 3 of the five items, the sproc needs to build something like this:
WHERE Region LIKE Item1 OR Region LIKE Item2 OR Region LIKE Item3
I cannot figure out how to do this. It works properly if I just make the ListBox SelectionMode as Single and pass that one selected item's value.
Any help is greatly appreciated.
Thanks,
Chris
View 3 Replies
View Related
Feb 10, 2014
I have a requirement that if in a table update happened based on 1st condition then it should insert in one way and if update happened on second condition the insert statement will differ. That is it should insert the deleted records i.e., previous records existed in a table.The syntax is like
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
BEGIN TRY
IF NOT EXISTS
[code]....
I m getting some syntactical errors.
View 6 Replies
View Related
Feb 24, 2008
Hello all,
I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.
Imagine a site that tracks movies. I have 3 tables:
Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)
This is an overly simple example, but it gets to the heart of my problem.
Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance
create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )
that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry
Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.
Thanks.
View 3 Replies
View Related
May 28, 2008
Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.
I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.
The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END
Thank You.
View 3 Replies
View Related
Oct 6, 2005
I have a form where a user can select multiple items from a listbox control.How can I pass each item selected to a sproc? Do i need to created a paramter for each item in my listbox in my sproc?Has anyone done this, I dont want to create dynamic sql to handle this and i dont really want to create 100 parameters to handle my listbox items.thanks
View 1 Replies
View Related
Oct 12, 2015
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
View 19 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Apr 30, 2008
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
View 2 Replies
View Related
Jul 30, 2007
Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form
AFTER INSERT
AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)
--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)
View 5 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Dec 29, 2006
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
View 3 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Feb 10, 2008
Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
View 1 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
Mar 24, 2008
Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
View 2 Replies
View Related
May 3, 2008
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
View 6 Replies
View Related
Nov 30, 2000
Can anyone assist me in solving this problem:
DTSTransformCopy: ValidateSchema failed; see Extended Error Information.
TransformCopy 'DTSTransformation_10' validation error: Source column too narrow to contain a valid value of destination column's datatype for column pair 1 (source column 'Col010'(DBTYPE_STR), destination column 'CHECKDAT' (DBTYPE_DBTIMESTAMP)).
Any help would be greatly appreciated.
Thanks,
Rey
View 1 Replies
View Related
Nov 19, 2003
I need to perform some division on the results of two alias columns that perform counts in a query. What is the best way to do this?
View 7 Replies
View Related
Sep 3, 2002
How to delete a record from a parent table, all relating records in the child tables should also delete.
How to do it by MSSQL 7.0
ANB
View 1 Replies
View Related
Mar 8, 2005
I have two tables that I have to compare:
Table:PR
WBS1 WBS2 WBS3
123-456 1000 01
123-456 1000 02
123-456 2000 02
567-890 2000 01
567-890 2000 02
Table:PR_Template
WBS2 WBS3
1000 00
1000 01
1000 02
2000 00
2000 01
2000 02
After Insert I should have:
wbs1 wbs2 wbs3
123-456 1000 00
123-456 1000 01
123-456 1000 02
123-456 2000 00
123-456 2000 01
123-456 2000 02
567-890 1000 00
567-890 1000 01
567-890 1000 02
567-890 2000 00
567-890 2000 01
567-890 2000 02
Basically, I need to insert the wbs2 and wbs3 where it does not exist in each wbs1.
What I have now will find the values that need to be inserted for a particular project but I don't know how to go through each project and perform the insert:
Select * from PR_template Where Not Exists
(Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')
Order by wbs2, wbs3 asc
Thank You
View 11 Replies
View Related
Mar 26, 2008
Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.
ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am
The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!
View 3 Replies
View Related
Sep 4, 2013
I need to do SUM on a column values and then subtract it from the SUM of values on a column from another table. I am using SQL server Management Studio, but getting the errors on the following Query
SELECT note_id, cap_int_amt)- SUM (ttl_cap_int_amt) as SUM1,
sum(orig_fee_amt)-sum(ttl_qualfyng_fee_amt) as SUM2
FROM [spstrd00_starrpt].[dbo].[rpt23t]
where [rpt20t].note_id =[rpt23t].note_id
ERROR Message
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.rpt23t'.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "rpt20t.note_id" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ttl_qualfyng_fee_amt'.
View 4 Replies
View Related
Oct 10, 2007
Hello,
I want to write a query that joins data in a different table based on a column value. The table is for a "Playlist" and holds play list items. The items can be video, audio, images, etc. The playlist table looks like this:
Table_Playlist
-----------------
ID (int)
MediaType (char)
MediaId (int)
Table_Audio
-----------------
MediaId (int)
Table_Video
--------------
MediaId (int)
If the Table_Playlist.[MediaType] column value = "Audio" then I want to join to the Table_Audio table. If the value = "Video" then I need the video table.
Hope that makes sense. Thanks
View 1 Replies
View Related
