Swap Column Position
Jul 20, 2005
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.
Thanks.
View 6 Replies
ADVERTISEMENT
Jul 26, 2007
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?
View 6 Replies
View Related
May 26, 2008
HI All,
can we interchange the column position in sql.
View 4 Replies
View Related
Dec 12, 2007
What is the SQL for adding a column at specific location?
eg
TableA
colA
colB
I want to add colC after colA.
Thanks,
Max
View 1 Replies
View Related
Jun 1, 2007
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
View 1 Replies
View Related
Dec 16, 2004
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?
View 2 Replies
View Related
Jun 18, 2008
1 have a table with 74 colun.But due to some deletion i have the ordinal postions as
1
2
3
.
.
69
70
72
73
74
76
what i wants is to change the ordinal from 72 to 71,73 to 72,74 to 73,76 to 74
SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
-- handle insert case here
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
I needed it as i have a trigger whihc is adding value in a log table on update but due to this problem it fails for certain column
Trgger is solme thing like
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
BEGIN
IF @Type not in ('I')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @field -1
--print('fieldname = '+@fieldname)
SELECT @sql = 'insert Audit (ActionTypeID, RowID, TableName, PK, FieldName, OldValue, NewValue, UpdateDate,UserID, UserName, UserType)'
SELECT @sql = @sql + ' select convert(bigint,' + @ActionTypeID + ')'
SELECT @sql = @sql + ',' + @RowID
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',' + @UserID
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @UserType + ''''
SELECT @sql = @sql + ' from #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
--print('@sq=====sdfdfsfsdf')--sha
--print(@sql)--sha
EXEC (@sql)
END
END
END
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 9 Replies
View Related
Apr 16, 2012
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.
View 12 Replies
View Related
Dec 12, 2007
Hello!
Is there a function that gets the name of a column and a string as arguments and returns the position of this string in the column given?
Thank you in advance.
View 1 Replies
View Related
Feb 26, 2008
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 ?
View 2 Replies
View Related
Aug 30, 2007
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 ..
View 6 Replies
View Related
Jun 27, 2006
Hi,
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!
View 6 Replies
View Related
May 19, 2005
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
View 3 Replies
View Related
Feb 3, 2008
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.
View 2 Replies
View Related
Apr 30, 2004
Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).
View 12 Replies
View Related
Oct 30, 2006
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.
View 5 Replies
View Related
May 12, 2008
have the following table:
RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8
etc....
I need to transform it to this:
1 2 3
Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8
Can this be done automatically in SQL?
View 3 Replies
View Related
Sep 24, 2007
I have a table named tbOrders. I want to swap the data between 2 of its columns named OrderNumber and CaseNumber.
Both the columns are varchar datatype. How can i swap the data between the two columns?
View 5 Replies
View Related
Sep 10, 2015
I am a newbie to Sql server and i am having a task where my current table with always two rows existing and ID column as primary key looks like below:
ID PlateNo Type Image Name
27 455 User img1.jpg
32 542 Alternative img2.jpg
And i want a sql query to modify my table so that the data should be like as shown below:
ID PlateNo Type Image Name
27 542 Alternative img2.jpg
32 455 User img1.jpg
View 8 Replies
View Related
May 14, 2008
please need rescue- complex update logic
this is my table
1
2
3
4
5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
W
Y
P
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
value_swap
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
A
B
C
G
H
I
J
K
L
M
N
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
7
8
9
C
E
Y
I
O
W
Y
P
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
=1
=2
=3
=4
=5
EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44
fld444
fld5
fld55
fld555
1111
A
B
C
7
8
9
G
H
I
J
K
W
Y
P
2222
N
M
L
K
J
I
H
G
F
E
D
C
B
A
3333
1
2
3
A
B
C
C
E
Y
I
O
L
M
N
TNX FOR ALL THE HELP I GET IN THIS Forum
View 7 Replies
View Related
Oct 9, 2015
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 ?
View 3 Replies
View Related
Jun 12, 2015
I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID Name Value
--- -------- --------
1 name1 202
2 name1 203
3 name2 203
4 name2 202
5 name3 204
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
WHERE
Valuein(202,203)
View 10 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
Oct 25, 2007
[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.
View 12 Replies
View Related
Jan 26, 2004
hello everyone,
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
View 1 Replies
View Related
Apr 11, 2007
Hi,
I'm trying to get the position of a single value in the query results.
like this:
select * from tPerson order by ds_alias
results in
id_person | ds_name | ds_alias
15 | mark | AA
20 | john | AA
5 | mike | BB
8 | thomas | JK
2 | mike | MM
as you can see, I can have registries with the same name and with the same alias, there's no restriction
and I'm trying to find out how many registries there are before the second "mike", wich would be 3.. so it's the 4th element, get it?
I have no idea how to...
Thank in advance
View 5 Replies
View Related
Jun 21, 2004
Hi,
Is there an easy way to get the position (or number) of the week from a date? By position, I mean week number 1 if we are january 1st. For example:
Date | Week postion
=====================
2001-01-05 | 1
1996-12-28 | 52
1987-06-15 | 26
Thanks a lot,
Skip.
View 1 Replies
View Related
Feb 7, 2005
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.
Bye and thanks...
:D :D
View 4 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Oct 19, 2007
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?
Thanks!
View 3 Replies
View Related
Nov 16, 2001
Hi Everybody,
Is there any way to find the current position of a row in a table?. Like what we have in Oracle ROWID, Like that anything available in SQL Server.
I have seen NEWID(),uniqueidentifier & ROWGUIDCOL in BOL. Apart from that any easy method is available in SQL Server?
thanks in advance,
Vasu
View 2 Replies
View Related
Aug 5, 2004
Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regards
View 5 Replies
View Related
Feb 26, 2007
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."
View 3 Replies
View Related
