What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
....
Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.
There are about 500 tables in one particular datbase. There are foreign keys set on most of the tables. I want to change the position of the primary key column in all those tables. How can I do that programatically?
I created SQL table follow by XSD fileAnd when any users added new column to XSD in ordinal position = 3 But after my program successfully created new column, its position is the last position What can I do ???? I suspect why I can't set it (I've looked for solution on MSDN already)even though We can see ordinal position bythis query SELECT *FROM INFORMATION_SCHEMA.Columns What can I do for solving ???? Help me please
We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc.
One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?
Is there any way to select columns by ordinal position rather than by name?
Something like Select t.[1] as col1, t.[2] as col2 FROM table t
The question comes because of a very specific situation where I've got 2 databases that have exactly the same schema at a column/datatype level but the column names are different. There are a few other ways to skin the cat but I'm interested if it can be done this way - without a join to syscolumns.
I have a Stacked Column graph and when we set the Position of the Point Label to be "Top Center", the Point Label always stays in the center of that particular Bar.
On a easier, basic Bar chart, the Point Label setting works correctly.
Has anyone else had a similar issue or know if this is a known issue ?
Hi I am having a problem in auditing the column data in tables.My requirement is i have write a trigger which is capable of auditing the columns which are going to be added in the future also with out using dynamic SQL.is there any way to do so. I feel if i can get the column data based on ordinal position then it is possible. Can any body suggest. My set Up is like this I have a base_table to be audited. I have a Audit_spec table which contains name of the table and columns to be audited. And Audit table which actually captures the table name,column name ,old value and new value. I have to audit only those columns in the Audit_spec spec. If schema changes(Like new column added) happens to base_table and I want that column to be audited.with out any changes to my trigger code i should handle the newly added column ..
I'm pretty new to SQL and certainly new to SQL Server, so this is no doubt a simple question, but how can I take a row of data (say row 117) and swap it with another specified row of data? I know it sounds odd, but the key for each row is automatically generated so I don't want to mess with that, so I just want to swap the other fields.
Here's an example of what I mean:
---------------------------------------------- ID | headline | content | newstype | date | ---------------------------------------------- 117 | test | asdfgasd| industry | 06/06| 118 | test2 | adsfg as| company | 06/06| ----------------------------------------------
What i've discovered is that the order of data in the rows isn't correct, so I want to maintain the ID fields seeing as they're in numeric order, but then use that ID to grab the rest of the row (117 for example) and replace the data for 118 with 117's data. Then do the same with 118's original data....
I need to do this because all the related rows in my table were posted on the same date, so other than that they appear on screen based on where they appear in the table, which doesn't match what the client wants...
Sorry for the long post, but as a beginner with SQL I wanted to make sure I made sense!
I need to swap two rows in a table so that i can give the user a choice to move the row data up and down. The top and bottom row values may not be in the serial order. I am using MS Sql 2000. Is there any way to do that or its really difficult big process. I really stuck with this problem can you help me out, please friends.. Your help will greatly appreciated.. Thank you very much
hi, we have got a new server at work running SBS 2003. the old server was SBS 2000 and it has a database on it. Both are running the SQL server program. Can anyone give me instructions to transfer these over (i have no idea how). i realise this has probably been asked before but i dont have the time to search so if someone could link a thread with instructions that would be good. cheers.
I have setup logshipping in sql 2005. i have a primary and secondary server only. Suppose for maintenace i bring primary down. . i have to manually bring the secondary server online. usnig restore databse with restore.
1) so far so good. But what now. How can i re-sync the old primary with the new primary?
2) can i return the original secondary back to standby mode so that the primary can resume its role and the log shipping process proceeds.
i need to update for example the eployee 1111 with employee 3333 but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 in field- fld2,fld22,fld222 )
Code Snippet ---update eployee 1111 with employee 3333 -so if i put the value 2 than ------------------ swap value between 2 employee set empid1= 1111 set empid2=3333 value_swap=2 if value_swap=2 than update fld2,fld22,fld222 with fld2,fld22,fld222 ------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333 --------------------in field- fld2,fld22,fld222
Code Snippet ---update eployee 2222 with employee 1111 -so if i put the value 5 than ------------------ swap value between 2 employees set empid1= 1111 set empid2=2222 value_swap=5 if value_swap=5 than update fld5,fld55,fld555 with fld5,fld55,fld555 ------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333 --------------------in field- fld5,fld55,fld555
I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A] [Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest SET Value = CASE WHEN Value =202 THEN203 ELSE202 END
We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
we have a problem in my system that will say it now.
we have two departments can access to the same database , the first department fill all the fields in the application(web_enabled), the second department can fill some fields from whole the fields and the remaining fields are set as default values because they don't have access to the remaining items. the problem here, when the first department go to fill the fields and then press save to save the filled information , will operation successful, but when the second one want to fill the fields then click save , will appear this error " there is no row at position 0".
note that we can't to change any thing in the code , i hope to find the answer for our problem thru database. with my regards
Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?
My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.
Is there a neat way to find an ordinal value from a table,for example the median or 95th percentile value in a column,without walking through the table in ascending or descendingorder?Thanks,Jim GeissmanCountrywide Home Loans
When i use the "run package task" to run a package in the sqlserver storage,the package will run at the storage server or the server call this package?
khosara writes "I have one parameter @String with value "My name is Khosara". How to get the value only "Is khos". Could you please help me, witch method shold i use. Thank in advance."