How Do I Insert Additional Table Between 2 Others?
Mar 10, 2006
Part of the code as follows:
SELECT
ARDoc."Cpnyid", ARDoc."Custid", ARDoc."CuryOrigDocAmt", ARDoc."DocBal", ARDoc."DocDate", ARDoc."Doctype", ARDoc."slsperid", ARDoc."Territory", ARDoc."RecordType", ARDoc."user7",
ARTran."CmmnPct", ARTran."CuryTranAmt", ARTran."DrCr", ARTran."ExtCost", ARTran."InvtId", ARTran."JrnlType", ARTran."Qty", ARTran."RefNbr", ARTran."Rlsed", ARTran."S4Future04", ARTran."S4Future05", ARTran."TranAmt", ARTran."TranClass", ARTran."TranDate", ARTran."TranType", ARTran."UnitDesc", ARTran."UnitPrice",
RptCompany."CpnyName", RptCompany."RI_ID",
Customer."Name",
Salesperson."CmmnPct", Salesperson."Name", Salesperson."SlsperId"
FROM
{ oj ((("SOLUSBS02APP"."dbo"."zARDoc_Comm" ARDoc INNER JOIN "SOLUSBS02APP"."dbo"."RptCompany" RptCompany ON
ARDoc."Cpnyid" = RptCompany."CpnyID")
INNER JOIN "SOLUSBS02APP"."dbo"."Customer" Customer ON
ARDoc."Custid" = Customer."CustId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."Salesperson" Salesperson ON
ARDoc."slsperid" = Salesperson."SlsperId")
LEFT OUTER JOIN "SOLUSBS02APP"."dbo"."ARTran" ARTran ON
ARDoc."Custid" = ARTran."CustId" AND
ARDoc."Refnbr" = ARTran."RefNbr" AND
ARDoc."Doctype" = ARTran."TranType"}
Currently, if a new rep takes over for an old ones invoices and accounts...he will also get credit on the report which this query is for. Instead I need to use a table SOShipHeader to be 'date sensitive'. SOShipHeader will have the correct 'SlsperID', but will still need to pull the name from Salesperson."Name"
My guess, would be that I need to wedge the SOShipHeader table between the ARDoc and Salesperson tables?
View 5 Replies
ADVERTISEMENT
Apr 24, 2008
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
View 9 Replies
View Related
Aug 11, 2015
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
View 2 Replies
View Related
Apr 21, 2007
Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one. So I created a user here on www.asp.net just for making this post.
Before I continue I just want to apologies if there is another post where this question is already answered.
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “
As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.
The MDF file is located in the App_data folder and is called ASPNETDB.
And when I try to add custom SQL statements, it gives me Syntax error near “=�. Something like that. I bought the Total Training Set1 package and it works perfect in their examples.
I just want to thank you for reading my post and I hope that you got some useful information for me.
By the way, I’, from Sweden so you have to excuse me if my English is rusty.
Thanks!
PS: Can it be that I’m running windows Vista?
View 4 Replies
View Related
Nov 12, 2007
Hi! This is my trigger and I'd like to insert the date of today in Column DeletedDate. This trigger is in tblA. tblA and tblB both had the same number of columns and same fields, but I just added another column to tblB called deletedDate and i'd like to insert the date along with the other data. Thanks!!!
Insert into tblB SELECT* FROM Deleted
View 2 Replies
View Related
Feb 27, 2008
I added one crummy column to my table. I updated the stored procedure and added the thing to the aspx page which is my form for adding an article. I have a strong feeling that something is foul over here...Why is it that Visual Studio will not allow me to capitalize the word get and when I delete the () after ShortDesc they immediatlye reappear and the get statement is set to get. Every other one of them, and there are 9 others use GET and there is no () after the public property variable name. Does anyone know the reason for this?
Public Property Author As System.String
GETReturn _Author
End Get Set(ByVal Value As System.String)
_Author= ValueEnd Set
End PropertyPublic Property ShortDesc() As System.String
GetReturn _ShortDesc
End GetSet(ByVal value As System.String)
End Set
End Property
View 1 Replies
View Related
Jul 15, 2007
In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?
I do this but even Snapshot is not transfered to destination table!!
View 5 Replies
View Related
Mar 11, 2014
I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:
(A = Active, R = Repeat, L = liquidation......)
Here is a sample of the data I am trying to report off of:
CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]
[code]...
I have tried join to the same table - but I am still unable to get it to work.
View 1 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
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
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
Apr 20, 2008
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
View 1 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Jun 28, 2007
Help please!
I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).
the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????
i.e.
if request("dob") is "01/11/2007" (1st november 2007)
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
rs.open "tez", mc, 2, 2 rs.addnew
rs("dob") = request("dob")
rs.update
11 jan 2007 stored in table
while
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
results in
1 november 2007 is written to the table.
Both of these methods are used in the same asp page.
This is on a windows2003 server, sql2005,iisv6, asp.netv2
I have tried every setting I can find in iis,asp,sql server to no avail.
I need the recordset method to work correctly.
Terry
View 8 Replies
View Related
Oct 21, 2002
Currently both of our servers have 1 gig of RAM. We are adding and additional 1gig of RAM to our local server but none to the remote server. Transactional replication is performed between both servers in both directions. SQL Server is configured to dynamically configure memory on both servers.
Are there any issues or concerns that I should be aware of?
Thanks
View 1 Replies
View Related
Jul 6, 2005
I have code on a website that does a search to an Access database based on what the person enters as the search criteria. My SQL code uses a like % statement for all the fields in the search criteria. The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for..... I did read up and found that the data must be character for the like statement. Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE: <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5"> <tr> <td width="400" bgcolor="#3366cc" colspan="2"> <form method="post" name="DaForm" action="Makeit.asp" align="center"> <div align="center"><center><table border="1" width="350" cellpadding="3" bgcolor="#3366cc"> <tr> <td align="left" width="189"><select name="TypeSearch" size="1"> <option selected value="PartNumber">Part Number</option> <option value="Description">Description </option> <option value="Mfg">Manufacturer</option> </select></td> <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td> </tr> <tr> <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit" name="B1" value="Search!"><input type="reset" name="B2" value="Clear"> </td> </tr> </table> </center></div> </form> </td> </tr></table>Makeit ASP COde:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Your Search Results!</title>
<meta name="Microsoft Border" content="lb, default">
</head>
<body>
<%
Dim MySql
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("parts")
MySql = "SELECT * FROM parts"
If Request.Form("TypeSearch") = "Part Number" Then
MySql = MySql & " WHERE PartNumber LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "Description" Then
MySql = MySql & " WHERE Description LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
Dim rs
Set rs = new Server.CreateObject("ADODB.Recordset")
rs.Open MySql, cn, 0,1
%>
<% If rs.BOF and rs.EOF Then%>
<h2 align="center">We did not find a match!</h2>
<%Else%>
<%If Not rs.BOF Then%>
<h2>Here are the results of your search:</h2>
<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th>
</tr>
<%
Do While Not rs.EOF
%>
<tr>
<td><%=rs("PartNumber")%>
<%=rs("Description")%>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
<p> </p>
</body>
View 1 Replies
View Related
Feb 28, 2001
Hi,
What are all the performance parameters to be analyzed, in an existing production server, to accommodate an additional database.
Regards
Kalyan
View 1 Replies
View Related
Feb 12, 2014
I have two tables that I am pulling data from to insert into a third table.
Table 1:
Organization Address Date
Name 404 St. 12/31/1999
Table 2:
Organization Software Type Quantity
Name SW1 5
Name SW2 6
Name SW3 7
Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity)
SELECT Table 1.Name, Table 1.Address, Table 1.Date,
(Select Table 2.Quantity Where Table 2.Software Type = 'SW1'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW2'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW3')
FROM Table 1, Table 2
WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
View 2 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales_HW| -20,000.00 | 01 | 2005
===================================================
1000| Sales_SW| -10,000.00 | 01 | 2005
===================================================
1001| Cost_HW | 5,000.00 | 01 | 2005
===================================================
1001| Cost_SW | 5,000.00 | 01 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Sales Category | Sales | Cost | Profit
=================================================
HW |-20,000.00 |5,000.00| -15,000.00
SW |-10,000.00 |5,000.00| -5,000.00
=================================================
Total |-30,000.00 |10,000.00|-20,000.00
The above report have 4 columns, with last column being a calculated field (Sales-Cost)
Guys, hope someone out there can help me with the sql command for the above report?
View 2 Replies
View Related
Feb 20, 2007
How do I create additional instances of any MSSQL 2005 service(specifically database and analysis) given that a default instance is present?
View 4 Replies
View Related
Jul 31, 2006
We are
looking for a solution for a client that we will make software for. We are a ISV. After
completion of the software, our client then wants to sell this software to their
partners, people in that business, etc. We do not want to add any additional cost, by means of them(client(s)) purchasing
a server, server software or licensing. Is there any solution
that will make this work? A runtime or embedded database solution? If there is a
license we need to purchase, what would that be? SQL Express Edition work?
View 4 Replies
View Related
Jul 6, 2006
Hi
I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg
12234
122334343
1234234567
how can i get it to look like this adding an 0 to the front to make the row 10 digits
0000012234
0122334343
1234234567
Thanks
View 6 Replies
View Related
Feb 29, 2008
Hi All,
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far.
Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end).
I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
Thank you.
View 1 Replies
View Related
Dec 28, 2006
I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?
View 2 Replies
View Related
Jul 23, 2007
Hi, I use the FOR XML AUTO to retrive native XML from a database with:
SELECT [xml] FROM myxml WHERE id = 81 FOR XML AUTO, elements, root('ROOT')"
However it returns the database name and table name as parent elements. How can I return just my raw XML data without additional elements:
XML is Stored:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
Returns:
<databasename>
<tablename>
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE> </CHAPTER>
</ROOT>
</tablename>
</databasename>
View 1 Replies
View Related
Jan 18, 2008
I have a stored procedure which is used to search records in the database based on the data sent from the web page. There are several search fields and all of them are in one table (Table1) except the "CallerName" field which is in a different table (Table2). Since I had to show CallerName also in the gridview apart from other columns, I did a LEFT JOIN (using field CallerNumber) to show all the required fields from Table1 and CallerName from Table2.
Now heres the problem. Since CallerName is a search criteria, its supposed to be in the WHERE clause after the JOIN or in the JOIN clause itself. The problem is, if I put it in WHERE clause, the result set doesn't show records from Table1 which do not have a matching CallerNumber in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber
WHERE T1.CallerNumber = 'some number' AND T2.CallerName = 'some name' If I put it in the JOIN condition, it works just like a LEFT JOIN is supposed to work, showing all the records in Table1 and also those which had CallerName in Table2. SELECT T1.CallerNumber, T1.DateCalled, T2.CallerName
FROM Table1 T1
LEFT JOIN Table2 T2 on
T1.CallerNumber = T2.CallerNumber AND T2.CallerName = 'some name'
WHERE T1.CallerNumber = 'some number'
1st SQL won't work for me because it doesn't show all the records in Table1, even when no search criteria is specified.2nd SQL won't work for me because it shows more than required when just CallerName is sent from the web page as search criteria. It will show all the records where CallerName is "some name" and also all the additional records (since it is a left join).
Can I get the goodness of both in one or do I have to create two separate Stored Procedures?
Thanks all,Bullpit
View 11 Replies
View Related
Dec 18, 2003
I have a DataGrid, it will return a table in SQL2000 server.
the problem is:
no data in the datagird, only a record "null" in all field.
I find a sample is add a "sqlDataAdapter1.Fill".
but if I add the code:
sqlDataAdapter1.Fill(dataSet21);
I got the error:
---------------
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
-------------
I have search this problem fix many many time, many people has this problem, looks like no result I can find.
Please help, thanks very very much.
Thanks.
View 2 Replies
View Related
Jan 16, 2006
I have a stored procedure that uses a dynamic order by statement. This statement works okay until I try to select ticket's by userEmail which is passed in to my stored procedure as a parameter. Here is the code that works for my dynamic sort order:
CREATE PROCEDURE [dbo].[SelectAllTickets]
@SortOrder varchar(250)
AS
SET NOCOUNT ON
Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,
b.typeID, b.typeName,
c.userID, c.UserFirstName,
f.statusID, f.statusName
FROM Tickets a
LEFT OUTER JOIN Type b ON b.typeID = a.typeID
LEFT OUTER JOIN Users c ON c.userID = a.userID
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
ORDER BY ' + @SortOrder)
I modied this procedure to create one in which I select tickets based on the userEmail as a criteria as well.. this one fails due to Incorrect syntax near the keyword 'BY'
CREATE PROCEDURE [dbo].[SelectTicketByUser]@SortOrder varchar(250),@userEmail varchar(50)
AS SET NOCOUNT ON Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID, b.typeID, b.typeName, c.userID, c.UserFirstName, c.userEmail, f.statusID, f.statusName FROM Tickets a LEFT OUTER JOIN Type b ON b.typeID = a.typeID LEFT OUTER JOIN Users c ON c.userID = a.userID LEFT OUTER JOIN Status f ON f.statusID = a.statusID WHERE a.statusID <> 40 AND c.userEmail = ' + @userEmail + 'ORDER BY ' + @SortOrder)
Any ideas on what syntax I should be using? Thanks!
View 3 Replies
View Related
