Rename Table, Fields, Queries Etc

Hi I have created a complete database with its tables, views, procedures, everything in SQL Server
I would like to rename a table and some of the fields however I need to address the changes in other tables and queries to reflect that
Thank you

View Replies


MDX - Rename Fields Name


My dataset is a MDX query that give me the results I need. However, I don't know how I can change the labels of the fields retrieved.
By default, the fields will be called like that in my report:

In the following query, I would like the year (2006 and 2007 in that example) to be a parameter, but if I add a parameter, the name change and I have an error in my report...


MEMBER Number AS sum([Measures].[# Contract for PR100])

MEMBER Amount AS sum([Measures].[Amount Contract E for PR100])

MEMBER [of which CAT Year N] AS sum([Measures].[Amount Contract E CY for PR100])

MEMBER [Average Value] AS Amount/Number



[Time Key Decision Date].[Calendar].[Year].&[2006],

[Time Key Decision Date].[Calendar].[Year].&[2007],

DESCENDANTS([Time Key Decision Date].[Calendar].[Year].&[2007],[Time Key Decision Date].[Calendar].[Month],SELF)

} ON 0,

Number,Amount,[of which CAT Year N],[Average Value]
} ON 1

FROM [BV Sales DW]

Is it possible to rename the fields?


View Replies View Related

Setting Identity Fields In Queries


I am relativley new to SQL and have a question about identity fields.

I am creating a script to run everynight to insert records into a support table in a database. one of the fields is a identity field that is updated everytime a record is added locally or over the web.

Some records that are added into the database locally by users do not get added into theis support table, but so those new people entered in can use the website a entry must be added to this support table.

I am working on a script to take the records that where added by users and automatically put them in every night using a basic schedueled job.

The identy number is updated everynight in a table that collects all the important identiy numbers. I would like to use this table to alter the seed value and then increment by one every time a new record is added. This is my only sticking point so far.

Any help would be greatly appreciated.
Kenn Brown

View Replies View Related

Merging Queries To Return Different Fields In Same

hi all,

can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.

1.number of imported imported animal type:

select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p

where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'

2. number of imported animal type that are licensed:

select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id

3.percentage: 2/1*100

select percentage=

((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100

4.local animal type:total pet records - 1

select number=

(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)

5.local animal type that are licenced:total pet records-2

select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)

6.percentage: 5/4*100

select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100


View Replies View Related

Calculated Fields In Queries Using Stored Procedures

I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.

I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.

How can I create functions to be used in my query?


View Replies View Related

Chart With Data Fields From Multiple Queries

Is there any way to create a chart using data fields from more that one query?

I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.

I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.



View Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View Replies View Related

Rename Table After Loading Data Into Temp Table

WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:

Begin Transaction

Lock Table XTable

Drop XTable

Alter Table XTable_temp rename to XTable

Release Lock XTable

End Transaction

Create XTable_temp

I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.

Any ideas on a process that might help?



View Replies View Related

Rename Table Name

I don't want to change datas in a table which I use, but want to change only the table's name with T_SQL statments.

As you know there are SPs for rename SP or view, but I can not find such SP for table.

If you know about it,
or have the another good method to rename table,
tell me please.

Thanks for reading.

View Replies View Related

How To Rename A Table?

We are running sqlserver 2000 EE on windows NT 4.0 server.

I need to to how to rename a table?


View Replies View Related

Rename Table

How to prevent a stored procedure or trigger from being invalidated after renaming a table.
I used sp_rename but it does not alter the stored procs and triggers using the table ,is there any way to change all the dependent objects without manually generating the script and then changing it

View Replies View Related


In MySQL I would issue this standard(?) SQL command to rename a table:

ALTER TABLE tablename RENAME TO footable

How is this done in ANSI SQL?

View Replies View Related

Hoe To Rename Table???


I am new in sql server.
I want to rename the table.
I use following commnad;

select * into e2 from e1

But some of the constraints are not transfered.
Is there any command like oracle?
Pleas tell me the solution.


View Replies View Related

Rename Table

Hi, I have written a service to rename table, how to check is the table is locked by another processes? There are some processes will execute select statement on it. The rename transaction must wait until all the select transactions have been finished.


View Replies View Related

Rename A Table

Does there has a way to rename a table name in sqlce 3.5 database ?
It looks like use sql alter command can not do this task .
Any one could give me advice are welcome.

View Replies View Related

How To Rename A Table ?

Hi all.

I'm porting an application from eVB-ADOCE to VB.Net-SQL CE

In the old application there are some SQL statements like this:

"ALTER TABLE OldTable TO NewTable"

Is there an equivalent instruction or a simple way to rename a table?

I was not able to find anything simple to do the same.

I really need NOT to do something like

SELECT * INTO NewTable FROM OldTable

because the resulting table could go outside memory and/or spend too much time in elaboration.

Please let me know what I can do.

Many thanks !

View Replies View Related

How To Rename A Table With TSQL?

Does anyone know how to rename a table with TSQL? I want to automate the renaming of a table.


View Replies View Related

Can't Delete,rename Table

i have a table called images
it has over 200,00 rows - i need to add 2 new fields and could not do it.
i created a new table and moved over the records but i can't delete or truncate or rename the table either

EXEC sp_rename 'images', 'imagesold' is just taking forever

please advise

View Replies View Related

Unable To Rename The Field Of A Table

i've SQL EXPRESS and want to rename the column/field of a table but it's not running. here is the query i'm using:  ALTER TABLE tablename RENAME COLUMN OldName TO NewNameand it shows me the below error:Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'column'. pls tell me how can i rename a field of a table. 

View Replies View Related

Dbo Schema Added When I Rename Table

i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05

my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one

1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.

DROP TABLE aMgmt.Employee
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'

any help?

View Replies View Related

Problem Occur After Rename The Table Name

I am developing a Mobile Application using vs 2005 and sql 2005

I have 1 table,after i rename it twice time,it was unable me to execute the SQL INSERT query....then i try to delete the table and recreate the table with the same name and the problem still cant resolve but after i create another table with diffrent name the sql query sucessfully exceute.

May i know what its the problem,and how to solve?

View Replies View Related

How To Rename A Column Or Table On The Desktop?

I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?


View Replies View Related

Long Query Rollback - Can I Rename The Table?

I have a long query which I have set off and would like to stop, andrename one of the tables used.My query is due to my lack of understanding of the underlyingstructure of MSSQL-Server...So say and update updates TABLE_A and I stop it, whilst thistransaction is rolling back I attempt to rename TABLE_A to TABLE_A_OLDand rename a different table to become TABLE_A. I am assuming thatthe rollback actions will use the object reference of TABLE_A_OLD andcontinue to rollback the effects on the correct table and not corrupt'new' TABLE_A... or will it not allow me to rename TABLE_A until therollback is complete?Thanks for any help!Steve

View Replies View Related

Rename A Table In Microsoft SQL Server 2005


Can you give the query in MS SQL Server 2005 which can be used to rename a existing table in database.

Thanks in advance,

View Replies View Related

SQL Server 2000 Table Rename Impact On Stored Procs

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table?

I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done...

Question 2

If you Truncate a Table as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollback? The theory is that it won't because Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?


View Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View Replies View Related

How Can I Update Several (not All) Fields In Table A From Table B For Replication?

I am trying to update the contents of table A (I'll useNorthwind..Products as an example) with the data from any changedfields in Table B (which is a copy of Northwind..Products, but withsome updated values). Table_A is replicated, so I would rather notupdate every field, but just the fields where the values are different.I could probably due it using dynamic SQL, but for many obvious reasonsI'd prefer not to. And the reason for Table_B is replication - when weget a "comprehensive" file we delete and start anew (and yes, there's areason for this as well), rather than just update the original.The only way I've thought of is to do one for each field, somethinglike:update productsset productname = products2.productnamefrom products, productswhere = and products.productname <>products2.productnameupdate productsset supplierid = products2.supplerid[...]Any help greatly appreciated. I considered trying some kind of CASEclause that would set it to its own value (set productname =products.productname), but that would (?) be considered a change andwould be replicated.

View Replies View Related

Creating Table Fields Dynamically From Another Table Row

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )

Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int....)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
n raj 45.................
================================================== ==

I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated....

Waiting for replies........

View Replies View Related

I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it

I need that query for alter table

in it

reply me soon

View Replies View Related

HT Insert Table A Record Fields Into Table B

All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)



View Replies View Related

Update Queries Using More Than One Table

In Access, if I want to update one table with information from another,all I need to do is to create an Update query with the two tables, linkthe primary keys and reference the source table(s)/column(s) with thedestination table(s)/column(s). How do I achieve the same thing in SQL?RegardsColin*** Sent via Developersdex ***

View Replies View Related

ALTER Table Queries

In Oracle, alter statements with modify clause can be given for more than one query ? Is it possible in SQL Server ?

Eg :-


Please give the equivalent for the above in SQL Server . Can all this exists in a single query in SQL Server ?

View Replies View Related

Copyrights 2005-15, All rights reserved