Solved

How do you reorder the ORDINAL_POSITION of a table?

Posted on 2008-06-23
5
402 Views
Last Modified: 2010-04-21
I have create a table that was created from a pivot sp that all works great the problem is I need to write a generic select that returns my pivot table with the first 2 columns in position 1 and 2. This is because Column1 and 2 are guarenteed,

See data below:
my fields are as follows:
Field              Ordinal Position
RackInfoID      1
Library A       2
Library B      3
Not Libraried      4
Saved Library      5

My data looks like this:
RackInfoID       Library A Library B    Not Libraried    Saved Library
ABC009407      NULL        NULL                   1                       NULL
ABC009408       NULL        NULL                  NULL           1    
ABC009409      NULL        NULL                   1                      NULL

So I want to  Return Column in this Order:
RackInfoID      1
Not Libraried      2
Library A       3
Library B      4
Saved Library      5
0
Comment
Question by:TraciShultz
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21848441
To change the ordinal positions of the columns in the table, you'll need to drop it and recreate it.  SQL Server doesn't have any internal constructs to reorder by columns.  When you do it in management studio, it creates a new table for you and swaps them out.
0
 

Author Comment

by:TraciShultz
ID: 21855708
I am usiong brut forse to change the ordinal position , I created the following SP. Which cretes a temp table and and through a cursor I am trying to add the additional fields. but I get the following Error:
"Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted."
Any idea's would be great.


ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)
                  
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]

            
      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT     TOP (100) PERCENT c.name AS ColumnName
      FROM         sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE     (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] AS int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1
END
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21855807
need to take out your AS statement:

'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] AS int NULL'

should be:

'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] int NULL'
0
 

Author Closing Comment

by:TraciShultz
ID: 31469864
Thanks so much. this really helped me out of a jam...
0
 

Author Comment

by:TraciShultz
ID: 21856056
Here is  the final solution.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:            TShultz
-- Create date:
-- Description:      
-- =============================================
ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)
                  
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]


      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT     TOP (100) PERCENT c.name AS ColumnName
      FROM         sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE     (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1
      
      INSERT INTO TempLibraryNew
      SELECT     dbo.TempLibrary.*
      FROM         dbo.TempLibrary

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibrary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibrary]

      SELECT * INTO TempLibrary
      FROM TempLibraryNew

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]
      

END
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now