Can I Move Some Column Position By Using SQL Command ?
Jun 1, 2007
I created SQL table follow by XSD file
And 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 by
this query
SELECT *
FROM INFORMATION_SCHEMA.Columns
What can I do for solving ???? Help me please
View 1 Replies
ADVERTISEMENT
May 2, 2008
Hello,
I have a table with the column description. Lets imagine that I have the records:
"a"
"b"
"c"
If this was a List and I wanted to move the "c" to the first position, I would simply remove the "c" and insert it in the 0 index position.
How can I do this with sql ce?
I could create another column named position and every time I would want to change the position, I would increment every other positions below. Example, "c" goes to first position:
position - description
1 - "a"
2 - "b"
3 - "c"
->
1+1 - "a"
2+1 - "b"
1- "c"
->
1- "c"
2 - "a"
3- "b"
But if I have lets say 100 thousand rows and I want to move the last one to the first position I have to do the operation 100K times.
Is there a more effective way to do what I want?
Thank you,
ARtur
View 9 Replies
View Related
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
Jul 20, 2005
What is a good method/mechanism to swap the position of multiplecolumns?For instance, tblXZY has the followings columns and respectivepositions:tblXZY======xyzUUID 1fn 2ln 3phone 4email 5city 6state 7....Now, I need to make city as 2 and state as 3. BTW, the tblXYZ tablehas data there. Copy/select all the data into a new table withdesired column position would require constraints re-mapping etc.,which seems quite a bit hassle.Thanks.
View 6 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
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
Aug 7, 2006
need some help converting a table which has one row for each year. Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns.
I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows):
Basic Information needed for Life Insurance 25 2005
Basic Information needed for Life Insurance 45 2006
Ea Chairmans Conference 10 2005
EA Chairmans Conference Press Release 33 2005
EA Chairmans Conference Press Release 21 2006
EA Chairmans Inner Circle Press Release 16 2005
EA Chairmans Inner Circle Press Release 46 2006
EA Honor Ring Press Release 13 2005
EA Honor Ring Press Release 35 2006
EA National Conference Press Release 6 2005
EA National Conference Press Release 9 2006
I need this data displayed like this:
Title 2005 2006
Basic Information needed for Life Insurance 25 45
Ea Chairmans Conference 10 10
EA Chairmans Conference Press Release 33 21
EA Chairmans Inner Circle Press Release 16 46
EA Honor Ring Press Release 13 35
EA National Conference Press Release 6 9
What I want to do is to basically say:
If Row 1 Title = Row 2 Title and Row 1 Year = Row 2 (Year + 1)) Then
add to grid Row 1 Title, Row 1 Qty, Row 2 Qty
Skip Row 2 and go to Row 3 (repeat this step until end of data)
End
Is there any way in the SQL querry to retrieve this data in this format so that I can display it in a datagrid?
Is there any way to display the data in this pattern.
Below is the code I am using now, but since the moving of the data is done in code it is very slow.
****************** Global Variables
Dim dbName As String = "Region_Web_Page_Hits"
Dim ColumnCount As Integer
Function ReturnColumnHeaderAndHitsDataSet() As System.Data.DataSet
'******** This will bring back the column Headers and Gross Hits per Column
Dim ColumnHeaderAndHitsQueryString As String = "SELECT DISTINCT TOP 100 PERCENT " & _
"YEAR(dat_Date) AS str_Year, COUNT(str_PageName) AS int_YearHits " & _
"FROM dbo.tbl_web_page_hits " & _
"WHERE (str_URL LIKE N'%press_Release%') " & _
"GROUP BY YEAR(dat_Date) " & _
"HAVING (Not (Year(dat_Date) Is NULL)) " & _
"ORDER BY YEAR(dat_Date)"
Dim ColumnHeaderAndHitsDataSet = LoadDataArray(ColumnHeaderAndHitsQueryString, dbName)
Return ColumnHeaderAndHitsDataSet
End Function
Function ReturnTableDataSet() As System.Data.DataSet
'This query string will return the data needed to display the report.
Dim TableQueryString As String = "SELECT TOP 100 PERCENT " & _
"str_PageName, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year " & _
"FROM dbo.tbl_web_page_hits WHERE str_URL LIKE '%Press_Release%' " & _
"GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName"
Dim TableDataSet = LoadDataArray(TableQueryString, dbName)
ColumnCount = TableDataSet.Tables(0).Columns.Count
Return TableDataSet
End Function
'******* Function to Load the Data Array
Function LoadDataArray(ByVal queryString, ByVal dbName) As System.Data.DataSet
Dim ConnectionString As String = ConnectionStr(dbName)
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString)
Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)
sqlConnection.Close()
Return dataSet
End Function
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Not Page.IsPostBack Then
Dim Sort_Field As String = ""
dg_Data.DataSource = QueryMethod(Sort_Field)
dg_Data.DataBind()
End If
End Sub
Currently I have a bunch of Classic ASP code which puts the data in to an array and then into a table with a series of if statments, but it is really slow.
I want to bind it to a data grid and if possible let the SQL statement format the data for me.
View 1 Replies
View Related
Aug 29, 2006
I have a table A I have Five columns and Each consist of Five rows of textcolumn 1column 2column 3column 4column 5Thru Sql coding i want to move from one column to another column - When Date Changes I need ASp and sql code for this Or ATleast i need sql query for how to move one column to another
View 1 Replies
View Related
Sep 12, 2004
Okay, after I got everything imported, I found that a few thousand columns had "Shifted" on me. So now I am trying to "Shift them over" to where they need to be.
I did this:
INSERT INTO dbo.TABLENAME (COLUMN_NAME_TO_BE POPULATED)
SELECT COLUM_NAME_OF_INFO_TO_BE_MOVED
FROM dbo.TABLENAME
WHERE MFG = 'MANUFACTURER_NAME' AND PN LIKE '8888888888'
GO
I populated the PN column with 8888888888 to use as a reference point, and the MFG column already was populated with the correct name, so I was using those as my unique reference points.
Other columns that have the same MFG name are correct, so I have to use two unique identifiers to specify the actual data that needs to be moved.
It inserted 'NULL' in the whole table after I ran it in the Query Analyzer. It appeared to disregard the WHERE statement all together
Any ideas on what I am doing wrong here?
Is there a way to move the data from one column to the next one over by specifying other WHERE criterias?
View 1 Replies
View Related
Nov 8, 2005
I am looking for a solution to move all Part Numbers for a specific manufacturer that is 9 digits long to a new column.
Let me explain............
I have a product table that has three columns of product codes associated with that product (Part Number as PN, Series as Series, and Industry Code as ICDE). I now want to create a fourth column (Manufacturer Code as MCDE) as my database has grown for another product specific numerical designator that is specific for one manufacturer.
Currently I have the specific 9 digit codes in the same column as the Part Numbers, though they all have their specific rows (Part Numbers & Manufacturer Codes are not in the same cell).
Now the Part Numbers have various numbers, letters, and special characters, but the specific 9 digit manufacturer codes are pure numbers.
So my question is............
How does one go about moving these specific 9 digit codes to their new column and out of the Part Number column?
They will all be exactly 9 digits, no special characters, no letters, no spaces.
Is there a way to tell MS SQL to just move anything for that specific manufacturer that has the 9 digit manufacturer numbers to the new column, bypassing anything with letters, special characters or any part number that is not a pure 9 digit number?
View 2 Replies
View Related
Oct 12, 2013
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID
from grpcon Inner Join groups
on grpcon.GroupNum=groups.GroupNum
View 1 Replies
View Related
Nov 10, 2005
I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1
May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS
Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2
ABCDE123 SER1
XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
Thank you.
View 4 Replies
View Related
Apr 4, 2007
If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?
ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?
View 3 Replies
View Related
Jul 20, 2007
Hi all, i have a database, the password column is normal format , and i want to update all the data in the column password to md5 format , can any one tell me what the command for me to do it ?
is this command will work ?
update tbl_MEMBERS set clm_password=(md5(clm_pass) where id<100
any one have ideas how to do it?
bbboy
View 1 Replies
View Related
Feb 23, 2006
Hi there,
I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.
For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -
update tableA
set columnA = ?,
column B = ?
where columnC = ?
AND (columnA != ? OR columnB != ?)
I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.
I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -
declare @ValueA varchar(50),
@ValueB varchar(50),
@ValueC varchar(50)
select @ValueA = ?,
@ValueB = ?,
@ValueC = ?,
update tableA
set columnA = @ValueA ,
column B = @ValueB
where columnC = @ValueC
AND (columnA != @ValueA OR columnB != @ValueB)
Any suggestions?
View 3 Replies
View Related
Jun 1, 2008
ALTER TABLE customers
add column active_flg int(1)
but i got error.
quote:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'column'.
what should i do?
ps. thanks for warning... i won't edit the post after i got the answer anymore.
View 6 Replies
View Related
Sep 12, 2013
I'm trying to add a column trough command "ALTER" and I've tried in many different ways.
Here is the result of statement..
View 1 Replies
View Related
Jul 3, 2006
Hi,
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
Please help me.
Thanks in advance.
View 13 Replies
View Related
Jul 19, 2007
Hi,
In my SSIS package,
1. I read from a source namely SOURCE. SOURCE is defined like
CREATE TABLE SOURCE
( f1 int, f2 int, f3 int, id int)
, where f1 is unique and id is always null after reading.
2. pass it to a Slow Change Dimension control to separate the new records and changed records
Then for new records,
3. link to a Derived Column control to add in some new columns
4. link to a OLE DB Command control to update field id before write to destination
In Step 4, I use the command :
INSERT INTO global_id_pool
SELECT MAX(id) + 1
FROM global_id_pool
UPDATE SOURCE
SET id =
(SELECT MAX(id) FROM global_id_pool)
WHERE f1 = ?
Using the command, the parameter can't be recognised with an error reported in BIDS. So eventually I had to change the query to the following to make it work
UPDATE SOURCE
SET id =
(SELECT MAX(id) + 1 FROM global_id_pool)
INSERT INTO global_id_pool
SELECT MAX(id) + 1
FROM global_id_pool
I don't mind the parameter can't be added. But after running, I found id field in the table after the OLE DB Command control is still NULL while the field in the SOURCE table in database is updated. So it seems the OLE DB Command worked on database but the data in memory cache wasn't affected.
So I just wonder if there is way to UPDATE the cache in OLE DB Command control. Many thanks for any help.
View 1 Replies
View Related
Nov 13, 2015
I can preview the SQL command in the OLE DB Source Editor and bring back all columns and results just fine but when I click on the Columns I get
TITLE: Microsoft Visual Studio
------------------------------
The component reported the following warnings:
Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.
The columns are there in the preview - why can't SSIS get the column information?
View 6 Replies
View Related
Jul 1, 2014
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'
SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],
[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[Code] ....
View 9 Replies
View Related
Nov 6, 2015
Is it possible to access a Derrvied Column from an OLE-DB Command? I have to Update a Table with a join and i need from the source Table columns which have to be pivoted before i can use it in the update Command.
View 4 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
