I imagine a data-retrieval scenario where data destined for several different tables are collected and initially placed into one 'collector' table (say, ALLDATA); a stored procedure then kicks in and passes the records to individual destinations. For example, I might want to have data for two persons, Ann and Betty, come in and be routed to table_ann and table_betty respectively, with the correspondence stored in table LOOKUP:
id output_table
Ann table_ann
Betty table_betty
How could this be implemented? Perhaps with some kind of loop over the rows of LOOKUP, with table name retrieved and entered into a (EXEC-written?) query
insert into <table_name>
from alldata
where id in (select distinct id
from lookup
where output_table = <table_name>)
But how could one iterate over the list of table names? (These could be placed in table, of course, and the question become 'how could one iterate over a table's rows?')
I hope that this is an easy question for SQL Server pros, and will be grateful for a brief pointer/code snippet. (I may have given more background then necessary, but perhaps there are better solutions available there as well?)
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.
declare @i int,@output varchar(max) set @i = 1 while @i < 10 begin set @output = @output + convert(varchar(max),@i) + ',' print @output set @i = @i +1 end
to create a code. Hi everyone, I have SQL SERVER 2005 on my desktop and i need to run queries from remote machines on my SQL SERVER. I thought it would be a good i idea to create a web page on my machine to where remote users can contact and activate SQL QUERIES. My question is very simple and i wonder i a simple answer is available: How do i run SQL queries whereby ASP code ? How do i make a connection between a web page (.asp code) and a SQL sever ? If the answer is complicated as i'm afraid it is, could any one provide me a link for a tutorial dealing with the above issues ? Thanks a lot !
Hi,I am trying to set up a DTS Package to transfer data from a remoteMySQL server to a local MS SQL database. The source tables will remainstatic as will the destination however I only want to copy down datathat does not already exist in the local database.I have two different methods of which to identify the new rows, on sometables it is by a unique ID thus in TSQL I would say WHERE ID x.Other tables are by date where I would want to do something like WHERECreateDate >= GetDate()-1How would I go about performing such clauses using DTS?ThanksMike
Hi,I'm enclosed a snippet of test code which highlights my problem. The Storedprocedure insertValue should insert text into the parent, then insert othertext into the child table but the 2 tables should auto increment in sync(i.e. so that they both end up with the same id numbers). I've tried takingthe auto increment out of the child table but then I don't know how to getthe right parent id into the child table.Any advice appreciated - this is my first database, so I'm just in thelearning process really. Code follows:CREATE TABLE Parent(id INTEGER DEFAULT AUTOINCREMENT,parenttext VARCHAR(16),PRIMARY KEY (id))!CREATE TABLE Child(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,childtext VARCHAR(16),FOREIGN KEY (childid) REFERENCES Parent(id),PRIMARY KEY (childID))!CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))BEGINinsert into parent (parenttext) values (p);insert into child (childtext) values (c);END!call insertValues('from parent', 'from child')!select * from parent, child where parent.id = child.childid!
I'm attempting to use a JOIN for the first time and I'm getting about 94,000 rows returned when I should only be getting about 270. Something must not be unique (possible DSN) but I can't figure out why the join is not working. I know it must be difficult to determine the problem without actually seeing the tables but could somebody with a lot of SQL experience spot any glaring problems with this query? Thanks!
SELECT DS.DSNName AS DSN, S.ServerName AS WebServer, DBs.DBName, A.ServerName AS SQLServer FROM DSNs DS, Servers S, DBs, DSNs LEFT OUTER JOIN Servers A ON DSNs.SQLServerID = S.ServerID WHERE DBs.Status = 1 AND DS.WebserverID = S.ServerID AND DBs.DBID = DS.DBID ORDER BY DSN
This has all the earmarks of a procedural error on my part. I have it occuring on two computers with the same sequence and exceptions and with clean databases. Note: The code is from Doug Lowe's ASP.NET 2.0 Everyday Apps For Dummies 2006 loaded from the CD. The database is generate from a script provided by the author. I will paste it in at the end.
This Q is so easy I can't find the answer anywhere! Why is SQL Server called 'Server' ? I understand it is a lot more capable and robust than Access but where does 'server' come into it. I currently use ASP and Access for dynamic websites but it is time to move up a notch. Do I just buy SQL Server, make a database, upload it to the same place as before and hey presto? can I run lots of websites (on different domains and servers) from databases created with my single license standard edition? Thanks M
I'm hoping you all can provide me with a little advice? I just landed my first DBA position with a company that runs different versions of SQL Server (7.0, 2000, 2005) on many different boxes. The databases I will be managing are in the 10-30TB ranges. Mostly the databases are for OLAP access by the company’s customers. I will need to bring myself up to speed on everything because there is no DBA at the company to transfer information.
Can anyone provide any advise on how I should approach this task of learning my position and/or useful links to resources? Thank you in advance!
I'm working on a website remotely through a VPN and my client uses SQLServer. There's a requirement for a database and my client uses SQLServer 2000.Is it possible to administer this database remotely, just like I dowith MySQL/PHP MyAdmin?Please elaborate.Thanks,Marjorie
Im not sure how to explain this or if this is the correct forum but here it goes.
I have created a report from a view that gives me court hearing dates on a defendent. When I run the report/view I get two different dates for the person such as, 12/14/2006 and 12/15/2006. How can I get it to show only the later date (12/15/2006)? I pull information from a mainframe that almost always gives me 2 different dates, but we only need the most recent date.
Here is my query for the view
SELECT dbo.CASES.CaseID, dbo.CASENUMBERS.CaseNumber AS Arrest_Number, dbo.CASENUMBERTYPES.CaseNumTypeCode AS Arrest_NumType, CASENUMBERS_1.CaseNumber AS JN_Number, CASENUMBERTYPES_1.CaseNumTypeCode AS JN_NumType, dbo.ROLETYPES.RoleTypeDesc, dbo.ACTORS.SIDNum, dbo.ACTORLOCATIONS.ActorLocDesc, dbo.CHARGENUMBER.ChargeDescription, dbo.DEFCHARGES.OffenseDate, dbo.fnNameReverse(dbo.ACTORNAMES.LastName, dbo.ACTORNAMES.FirstName, dbo.ACTORNAMES.MiddleName, dbo.ACTORNAMES.Suffix) AS NameRev, dbo.EVENTS.ev_StartDate AS PreHearingSetDate, dbo.OFFENSELEVELS.OffenseLevelDesc, dbo.CASES.UnitID FROM dbo.CASES INNER JOIN dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN dbo.CASENUMBERS AS CASENUMBERS_1 ON dbo.CASES.CaseID = CASENUMBERS_1.CaseID INNER JOIN dbo.CASENUMBERTYPES AS CASENUMBERTYPES_1 ON CASENUMBERS_1.CaseNumTypeID = CASENUMBERTYPES_1.CaseNumTypeID INNER JOIN dbo.CASEPARTIES ON dbo.CASES.CaseID = dbo.CASEPARTIES.CaseID INNER JOIN dbo.ROLETYPES ON dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID AND dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID INNER JOIN dbo.ACTORNAMES ON dbo.CASEPARTIES.ActorNameID = dbo.ACTORNAMES.ActorNameID INNER JOIN dbo.ACTORS ON dbo.ACTORNAMES.ActorID = dbo.ACTORS.ActorID INNER JOIN dbo.ACTORLOCATIONS ON dbo.ACTORS.ActorLocationID = dbo.ACTORLOCATIONS.ActorLocationID INNER JOIN dbo.DEFCHARGESUMMARY ON dbo.CASES.CaseID = dbo.DEFCHARGESUMMARY.CasesCaseID INNER JOIN dbo.DEFCHARGES ON dbo.DEFCHARGESUMMARY.DefChargeID = dbo.DEFCHARGES.DefChargeID INNER JOIN dbo.CHARGENUMBER ON dbo.DEFCHARGES.ChargeNumberID = dbo.CHARGENUMBER.ChargeNumberID INNER JOIN dbo.COURTS ON dbo.CASES.CaseCourtID = dbo.COURTS.CourtID INNER JOIN dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID INNER JOIN dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND (dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND (dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET') AND (dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
In the 'PRE-HEARING SET' table is where I receive the two dates. Is there a way to filter out the first date? I just want to show the most recent date.
Hi everyone, This is a beginner's question: I know that in order to run a package i need to press ctrl_f5 but i also know that there should be an icon (dimmed green arrow) to do that but i cant find it on the toolbar! I only see the "debug" green arrow. Can anyone help me find the lost "play" (or "run") green arrow ? on which toolbar is it situated ? Thanks
I need copy one database from one server into another server. Both servers have SQLSERVER 2000. One way is copy the LDF files directly but I need to stop the engine to do that, I€™m sure there are many other ways more efficiently. I do need an advice. Thanks in advance
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005 TABLES Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
I want to loop through a recordset and do inserts into another table based on each record.
The way I have been doing it is copy my key data into a temp table, Loop through temp finding the max ID Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.
I know there has to be a better way. The table I am working with is millions of records. Thanks in advance, Chris Reeder
I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.
During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?
Hello clever people I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this name compound_id integer name varchar(150) name_type integer
This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types 1 = chemical name 2 = a description of the chemical 3 = a synonym of the chemical 4 = a formula of the chemical
I have created a new table called compound_name with this structure
id int primary key (auto identity) compound_id int used as a foreign key compound_name varchar(150) compound_desc varchar(250) compound_synonym varchar(150) compound_formula varchar(50) compound_trade_nme varchar(50)
I have also started to populate the new table by running this code insert into compound_name(compound_id,compound_name) SELECT DISTINCT compound_id, name FROM dbo.name WHERE (name_type = 1)
Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2 Then case 3 do the same for name_type 3 which is the name_type for descripton Then case 4 do the same for name_type 4 which is the formula
Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:
select df.tablespace_name "Tablespace", block_size "Block Size", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from dba_tablespaces ts, (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name) fs where ts.tablespace_name = fs.tablespace_name and df.tablespace_name = fs.tablespace_name(+) ;
I know this question may have a very easy solution, but I have no idea how to solve it.
Bi Ar Ar Bi Ar Ch Bi Ar Ma Bi Au Ar Bi Au Ch Bi Au Ma As Ar Ar As Ar Ch As Ar Ma As Au Ar As Au Ch As Au Ma As Au Ma
I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?
Hi All, I would like to know the best way to approach the following requirement: I have an ASP.net 2 web site which gets its data from SQL 2005. I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing. The rules process will fire when the details have been submitted to the database. Table containg rules would contain something like: ID, RuleSQL 1, (ClientAge >18) 2, (ClientIncome>10000) 3 Etc... This a very simplified version of the table but gives the general idea. I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data. If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works. any comments or suggestions would be welcomed. Thanks, Ian
I have an array (12,2) of values plus a profile variable that I want to pass as parameters while writing to a database. I've been told that I've set up the parameters wrong, and they cannot be changed every time I loop using the method I'm using. But I have no idea how to use any other method. Please... I'm down to the wire in terms of deadline here. I have until midnight to get it uploaded and running online. [CODE]Sub WriteClasses(ByVal CreditsArray) Dim i As Integer Dim EnrollDb As SqlConnection Dim cmdEnroll As SqlCommand EnrollDb = New SqlConnection("Server=LONNASQLEXPRESS;Integrated Security=True;database=LGordonTouroReg") cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb) EnrollDb.Open() For i = 0 To 12 cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2)) cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0)) cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID) If Not CreditsArray(i, 0) = "" Then cmdEnroll.ExecuteNonQuery() Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>") End If Next i EnrollDb.Close() End Sub[/CODE]
lets say i have a stored procedure (for insert command) which i am calling in my code to execute. The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types. Is there any way to loop through the parameters, (various columns in the table which is of diffrent data type ie varchar, int, etc). How to implement it?
Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.
I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not Page.IsPostBack Then ' IF This is the first page load Dim UserID As String = Request.QueryString("UserID") ' parameter for stored procedure Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString")) MyCommand = New SqlDataAdapter("getdirective", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@roleID", SqlDbType.NVarChar)).Value = RoleID
Try DS = New DataSet MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click 'This need to loop through the records
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty WHERE item_no=@item_no
UPDATE Items SET Sales=@Total_Sales, Qty=@Total_Qty WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
<!--- Update the DISTANCE field on STORE table ---> <cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#"> exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#' </cfquery> </cfloop>
I am not sure if what I wish to do is possible, but I shall ask anyway;
My project examines a database log of all the pages of an online teaching tool. Once the user has completed all the pages they are to be issued a certificate. Users may complete the teaching tool in any order, and the pages are always stored whenever they are acccessed, regardless of certification. I have created a number of views that extract the data into a list of all the possible completion dates; i.e. where all the pages have been completed within any 12 month period. I need to write a query/view that uses the view to extract the first possible user completion date followed by every completion 12 months after that, then after that etc. to present day. Can I do this? Am I making sense ?
A no is acceptable in this case; I know I can do this with multiple queries from withing an application. I'd just rather not.
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
I've got one table with two columns. Column Name Data Type 1) Id Integer Identity 2) RemDate DateTime
I've to write one SP/JOB in that there will be an integer input parameter @numofday.
Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.
This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.
i have created a job that i have scheduled to run every 10 min everything is configured well since i have tested preety everything their is to be tested and found that it was my last step wich as a fetch in it so i imagine that this fetch is making it loop over and over again. the job goes trought all the steps and starts back at the first step and keep going like that till i disable it here is my fetch statement and if you have any clue any help would be widely apreciated.
PS: i suspected it to be that fetch statement causing the havoc ;)
DECLARE TransactionNb_cursor CURSOR FOR SELECT TransactionNb, EqId FROM DetCom WHERE UpdCode = 'C'
OPEN TransactionNb_cursor
FETCH NEXT FROM TransactionNb_cursor INTO @TransactionNb, @EqId
WHILE @@FETCH_STATUS <> -1 BEGIN -- Vérifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom IF (SELECT UpdCode FROM EntCom WHERE TransactionNb = @TransactionNb and EqId = @EqId) = 'C' BEGIN CONTINUE END ELSE BEGIN RAISERROR (50006, 10, 0, @TransactionNb, @EqId) END
FETCH NEXT FROM TransactionNb_cursor INTO @TransactionNb, @EqId END
CLOSE TransactionNb_cursor DEALLOCATE TransactionNb_cursor
1A ACTIVE 1BINACTIVE 1CINACTIVE 2BINACTIVE 2CINACTIVE 2DINACTIVE 3A ACTIVE 3B ACTIVE 3C ACTIVE 4B ACTIVE 5DINACTIVE ---------------------------------------------------------------- Following is the desired View that I need for the above table. Any ID which has atleast one ACTIVE branch will have ACTIVE status and any company which have all of its branches INACTIVE will have INACTIVE status. Thanks for your help
