Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.
The function of this, I believe was to make the select work but not retrieve any actual values.
I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.
What I am thinking may not even apply to what I need to do here.
If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.
Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table but I was hoping this would work.
Example:
Insert Into table Select 1 as RecId, ' ' as field1, field2 From test1 Where field2 = 'CA'
Union Select 2 as RecId, ' ' as field1, field2 From test1 Where field2 = 'NJ'
Union
/*Final Select */
Select 6 as RecId, ' ' as field1, field2 From test1 Where 'some value' = 1'
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible? My logical approach to this is: 1. Build a handler for the OnInsert event of the first table 2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource. What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
The application is running ADO (MDAC) version 2.81.1117.0.
The application gets an recordset created by SQLServer by an select statement. In this case the recordset is empty.
The application adds a record to the recordset by AddNew(). Works fine. But when assigning the first field (smalldatetime in database) I get ADO error -2147217887.
I have inspected the recordset and I found a difference between the working one from SQLServer2000 and the new one from SQLServer2005: The Attributes item for the fields in the recordset has the flag adFldUnknownUpdatable set in the working SQL2000 version but is not set from SQL2005.
The database on SQL2005 is copied from the SQL2000 system and attatched to the SQL2005 system. I Have checket that the compability flag for the database on the SQL2005 system is set to SQL2000 compability.
--Table 1 "Employee" CREATE TABLE [MyCompany].[Employee]( [EmployeeGID] [int] IDENTITY(1,1) NOT NULL, [BranchFID] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [MiddleName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeGID] ) GO ALTER TABLE [MyCompany].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID] FOREIGN KEY([BranchFID]) REFERENCES [myCompany].[Branch] ([BranchGID]) GO ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch" CREATE TABLE [Mycompany].[Branch]( [BranchGID] [int] IDENTITY(1,1) NOT NULL, [BranchName] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [ManagerFID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchGID] ) GO ALTER TABLE [MyCompany].[Branch] WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID] FOREIGN KEY([ManagerFID]) REFERENCES [MyCompany].[Employee] ([EmployeeGID]) GO ALTER TABLE [MyCompany].[Branch] CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID --generated IDs = GID Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee WHERE EmployeeGID= 39
Well this might look like a very basic error: I get this Error after trying to delete something from Table €śEmployee€?
The DELETE statement conflicted with the REFERENCE constraint "FK_Branch_ManagerFID". The conflict occurred in database "MyDatabase", table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €śBranch€? entry, basically each entry in €śbranch€? and €śEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €śON DELETE CASCADE€? or something?
I'm new in SQL SERVER 2000 and i'm looking for some kind of predefined dummy table. You know something like the "dual"-Table in Oracle. I want to use it for queries like that:
Select 1, 'auto' from dummy_table
Does anyone know, if something like that in SQL SERVER 2000 exists?
Is there any way to copy data from a previous saved recordset using "BULK INSERT" or bcp ? (the recordset is supposed to be saved using adPersistADTG or adPersistXML)
I am trying to figure out what SQL script on how can I do this........I want to insert a record in a table by using the text fieds instead of using a primary key to recognize where it relates to. Fro example, I have two tables: table Make---------------Make ID- Interger (PK) 1 description - text Honda table model----------------Model ID - integer (PK) 1desc.--text AccordMake ID - interger (FK) 1 I am trying to figure out A way to do something like this......... INSERT into model (Model ID which is an auto #, description, Make ID) VALUES (txtmodelID.text, txtdescripton.text, instead of doing MakeID here; I want to use the descripton from the Make table to insert thiis MakeID field) I badly need some help with this logic or syntax as it relates to most of the way I need to insert through out my site. Thanks in advance. In other words I want the user to select a Make from a drop down list not a Make ID# such as Honda that will insert under that Make ID number related to that description..... such as a type of car ; Accord's description into the model table.
Hi, I have 3 tables: Employees with the fields:idEmployee and employeeName Roles with the fields:idRole and roleName. An employee can have one or many roles. I created the table EmployeeRoles with the fields: id,idEmployee,idRole. idEmployee and idRole are foreign keys. I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ?
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
I want to transfer a recordset (derived from an Oracle datasource) into an SQL2000 Server table using VBScript in a ActiveX Script Task using a DTS.
Currently I use the OPENROWSET (and OPENQUERY) method, however the lenght of the querytext seems to be limited to 8192 bytes. At this moment I have reached the limit of this lenght, and I am looking for a solution.
Futher info: The query returns large recordsets of 100,000s of records with 100s of columns. Because of it's complex structure, the standard data transformation in SLQ2000 is not an option.
Using a substitute like:
varRecords = rst1.GetRows For intI = 0 To UBound(varRecords, 2) rst2.AddNew For intJ = 0 To UBound(varRecords, 1) rst2(intJ) = varRecords(intJ, intI) Next Next rst2.Update
I have two tables. In tblUsers is all userdata. In table tblMoreInfo is some info on a user (0-15 records).Now I want to select tblUsers.username and tblUsers.usercode and the matching top 1 row tblMoreInfo.schoolname (top 1 based on tblMoreInfo.createdate) IF IT EVENT EXISTS from tblMoreInfo. If no matching record exists I want the value NULL to be returned for the schoolname...In all cases tblUsers.username must be unique (and I cant use the distinct keyword as im already using the row_number() function.Any help?Thanks!
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...
I set up a table called relationships that looks like this:
ID type: int
RELID1 type: int, is the user id that initiated the relationship request.
RELID2 type: int, is the user id of the second person in the relationship.
Story type: varchar(255), quick blerb on how they are related.
Type type: int, a number 1-20 based on the relationship they have
Status type: int, 1 = confirmed by second person, 0 = not confirmed
What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems: 1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person. 2. I don't want to display them selves in their own relationship listing
Example Data: ID 1 2 3 4
RELID1 25 15 70 12
RELID2 54 70 13 8
Story Met on the east coast Met at walmart Met walking Met outside
Type 14 11 3 8
Status 1 1 1 1
Example Output: Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID". So: 15 = Bob Smith 13 = Jane Doe
The following code works great that I got with the help on another form:
SELECT Relationship.RELID1 , Relationship.RELID2 , ReMembers.FirstName , ReMembers.LastName FROM Relationship INNER JOIN ReMembers ON ReMembers.AccountID = Relationship.RELID1 WHERE Relationship.RELID2 = 70 UNION ALL SELECT Relationship.RELID1 , Relationship.RELID2 , ReMembers.FirstName , ReMembers.LastName FROM Relationship INNER JOIN ReMembers ON ReMembers.AccountID = Relationship.RELID2 WHERE Relationship.RELID2 = 70
My question is: What is the best way to set this table up? I'm not committed to any any design as of right now, but want to be sure I set it up in the most efficient manner.
I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:
Declare @tbl Table(id int, name varchar(100), age int)Â Insert Into @tbl(id, name, age) Values(1, 'James, Lee', 28), Â Â (2, 'Mike, Richard', 32), Â Â (3, 'Leon Wong', 29) Select * From @tbl Order By age
It works well in "SQL Query Ananizer". But return no records in ASP page.
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table. Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted? How can I achieve this?
Hi,I've got VS 05 web dev express installed and i'm trying the walkthroughs for login admin. I've succeeded and noticed the tables VS05 produces in the database ASPNETDB.MDF. I've created a new table "Customers" also with a UserID and also configured it as "UniqueIdentifyer" as VS05 has done in the table aspnet_users. The Customers table has two other fields: CustID (autoint) and CustomerName. Now i'm setting up a detailsview control that should insert a CustomerName but I also want it to insert the current logged in userID to the Customers.UserID field so that the aspnet_users and customers tables can be related. My question is how would my Sql insert statement look like to incorporate the parameter of the current logged in UserID and insert it into the Customers.UserID field?thanks.
Hey guys, I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items. I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId) I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable. Any help or suggestions would be great. Thanks, Matt
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block CREATE TABLE #gl_sizerange ( glsid int IDENTITY(1,1) NOT NULL, lowsize int, highsize int, sizecat varchar(10), milestone varchar(25), days int ) ON [PRIMARY] INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block CREATE TABLE #en_entity ( enid int NOT NULL, orgsize int, ) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5) INSERT into #en_entity VALUES(2, 18) INSERT into #en_entity VALUES(3, 24) INSERT into #en_entity VALUES(4, 25) INSERT into #en_entity VALUES(5, 47) INSERT into #en_entity VALUES(6, 101) INSERT into #en_entity VALUES(7, 499) INSERT into #en_entity VALUES(8, 500) INSERT into #en_entity VALUES(9, 10000) INSERT into #en_entity VALUES(10, 567890) CREATE TABLE #op_opportunity ( opid int NOT NULL, enid int NOT NULL, milestone varchar(25), daysopen int ) ON [PRIMARY] INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5) INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18) INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24) INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25) INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7) INSERT into #op_opportunity VALUES(6, 9, 'Close', 35) INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
Hi everyone! I'm on my way to learn SSIS by myself and it's a little complicated! I'd like to ask you one thing:
I have two tables at my data source, one is "Clients" and the other one is ClientsAddress. That is, a client can have more than one address. Both tables are related by a one to many relationship and the tables description is:
CLIENTS CLIENTSADDRESS #PK_Client #PK_Client other fields.. #ID_address ... other fields
What i intend to do is to obtain one table with approximately 3 fields, each one for a possible client address; something like this: CLIENTS PK_Client ID_Address1 ID_Address2 ID_Address3
My question is what transformation can i use? an how ?
Thanks very much in advance!! Emilio Leyes Salta, Argentina
