sql server update multiple columns

1. Using CursorsDECLARE @sql varchar ( $), @name varchar ( +) DECLARE my_cursor scroll cursor for SelectName fromsysobjectswhereType ='u'and name like'jobs_%'Open My_cursorfetch Next frommy_cursor into @name while(@ @fetch_status =0) BEGIN Print'processing'+@nameSet@sql = N'ALTER TABLE Testbfcmisuser.'+ @name + N'Add note varchar ($)'exec (@sql) print'finished

The first two days saw a blog post about how to convert a row of data that contains a single column of multiple values into a multi-row column in Oracle, with the following issues.ID number1 137xxxx,138xxxxTake it out.ID number1 137xxxx1 138xxxxThe solution given in the original text can be found in http://www.cnblogs.com/myjoan/p/4139348.html, and the reply seems to give a more concise wording.Because for several years did not touch Oracle, in recent

time 3523.2 ms　　　　Four. SummarizeYou can see that the larger the amount of data, the larger the paging page number, the greater the impact on paging efficiency. Top of the page method out early, it is obviously because the data in the not too large, so if there is a good top paging method can leave a message below, I will also test. Row_number page method is relatively good, and sql2005 and above the database can be used, the audience range is larger, and the offset fecth performance is more su

value is true. 6 Current Language (currently language): The name of the SQL Server language record. 7 Data source/server (server)/address (address)/addr (address)/network address (network addresses): The name or network address of the instance of SQL

server|sqlserver| process The process of automatically generating a number in SQL Server for multiple usersIf not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [indextable] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)CREATE TABLE indextable (Ex char (), num integer) Go CREATE PROCEDURE Setindex @

((Member INNER join Membersort on Member.membersort=membersort.membersort) INNER join Me Mberlevel on Member.memberlevel=memberlevel.memberlevel) INNER JOIN memberidentity on member.memberidentity= The memberidentity.memberidentity syntax format can be summarized as: from (table 1 INNER JOIN table 2 on table 1. Field number = Table 2. Field number) INNER JOIN table 3 on table 1. Field number = Table 3. Field number) INNER J OIN table 4 on Member. Field number = Table 4. Field number connection

Tags: data usage exp Data character rom specify CTE insertSample table TB data is as follows ID value—————1 AA1 BB2 AAA2 BBB2 CCC SELECT ID,[Val] = (SELECT [value] + ', 'From TB as BWHERE b.id = a.id for XML PATH ("))From TB as a Show results1 AA,BB,1 AA,BB,2 AAA,BBB,CCC,2 AAA,BBB,CCC,2 AAA,BBB,CCC, SELECT ID,[Val]= (SELECT [value] + ', 'From TB as BWHERE b.id = a.idFor XML PATH (")")From TB as aGROUP by IDShow results1 AA,BB,2 AAA,BBB,CCC, SELECT ID,[Val]=stuff (SELECT ', ' +[value ')From TB a

SQL Server database paging query has been a short board of SQL Server, idle, come up with several methods, assuming that there are table article, Field ID, year ... (other omitted), Data 53,210 (customer real data, quantity is not large), paging query each page 30, query page 1500th (ie 第45001-45030条 data), field ID cl

This is a recently written paging stored procedure that can be applied to SQL Server 2005: Copy Code code as follows: If object_id (' [Proc_selectforpager] ') is not null Drop Procedure [Proc_selectforpager] Go Create Proc Proc_selectforpager ( @Sql varchar (max), @Order varchar (4000), @CurrentPage int, @PageSize int, @TotalCount int Output

The following is a master in the SQL Bar solution, announced. For your reference: Let's say there's a list of school numbers, names, schools, three columns. And then insert it into the table. 040501 Sun Ming Shandong University 040502 Li Hao Shandong Normal University 040503 Wang Gang Yantai University How to insert these three rows of data Ah ~~~~~~~ Copy Code code as follows: Insert Table na

Whether you use int or varchar, multiple-selection queries for status are difficult to handle. For example, under normal thinking, the enum for Customerstatus is set as follows: Copy Code code as follows: [Serializable] public enum Customerstatus { New = 0, Active = 1, Overdue = 2, Suspended = 3, Closing = 4, Closed = 5 } The status value is stored as int in the database. What if I want to search the page for

1. Using functionsGoCREATE FUNCTION Dbo.fn_sumtype (@type varchar (50))RETURNS varchar (8000)AsBEGINDECLARE @values varchar (8000)SET @values = ' 'SELECT @values = @values + ', ' + Product_type from [DataCopy2] WHERE [email protected]RETURN STUFF (@values, 1, 1, ")ENDGOThe test is as follows:SELECT customerfullname, data = Dbo.fn_sumtype (Customerfullname) from [DataCopy2] GROUP by Customerfullname2. Using XML technologySelect Customerfullname, Data=stuff ((select ', ' +product_type from [DataCo

1--Create a test table2 CREATE TABLE [dbo]. [Testrows2columns] (3[Id] [int] IDENTITY (1,1) not NULL,4[UserName] [nvarchar] ( -) NULL,5[Subject] [nvarchar] ( -) NULL,6[Source] [Numeric] ( -,0) NULL7 )8 GO9 Ten--inserting test Data One INSERT into [Testrows2columns] ([Username],[subject],[source]) ASELECT N'Zhang San'N'language', -UNION All -SELECT N'John Doe'N'Mathematics', -UNION All -SELECT N'Harry'N'English', theUNION All theSELECT N'Harry'N'Mathematics', theUNION All -SELECT N'Harry'N'languag

Label:1: Add a Difference field, judge the Difference field to deleteDelete from (select Row_number () over (partition by code order by time DESC) as rows A, code, time from table) where a > 22: Query out more than 1 of the table data, query out the difference, according to the difference to deleteSelect Difference field from Table a right join (select Code, MIN (time) Minimum time from table group by SDDM have COUNT (code) >1) b on a. Code = B. Code an D A. Time = B. Time Order by a. CodeSQL