Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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 TABLEOldTable TONewTable"

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 thetable with the same name andthe 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 Iget 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
How Do I Copy A Fields From Another Table Into A New Table?

I want to put fields fromfour tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.

Table Fields

Tbl_Date_Dimension[Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact][Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id

The new table is called Tbl_Sales_Growth
Here's the information on the new table:

Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL

View Replies !   View Related
Two Queries From Two Different Databases = ONE Display/Table
What is the best approach to handle this situation?  I have three different databases, which has it's own stored procedure.  I need to call them all at page load and piece together the data.  The common demoninator is the date. 


row 1

row 2


row 1

Row 1 and Row 2 come from two different databases and stored procedures. 
How can I query the data and present it as it's shown above?
Thank you!

View Replies !   View Related
Help Using Dynamic Queries In Temp Table
The dynamic sql is used for link server. Can someone help. Im getting an error
( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )
--Create temp table
( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit )
INSERT INTO #TempTable (Name, AccountID, Active)
--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
                   FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
                   LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
                   ON a.AccountID = b.oaAccountID
                   WHERE oaAccountID IS NULL
                   ORDER BY Name ASC'
EXEC sp_executesql @sql
--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
Msg 156, Level 15, State 1, Procedure GSCLink, Line 22
Incorrect syntax near the keyword 'DECLARE'.

View Replies !   View Related
MULTI-Table Update Queries
I'm new to adp w/ sql server but I have to use it on a project i'mdoing...One of the MUSTS for this project is the ability to update a 00 - 09text value with the appropriate text description from another table...Easy as pie in .mdb. Of course In the stored procedure it barks at meand tells me that an update query can only have one table.. ouch thathurts...I'm currently reading on the subject but this group has been veryhelpful in the past.....I found this link... I'm using MSDE not Enterprise so I don't think I can usethe query analyser.. But I tryed it in my Access ADP anywayit barked at me..I tried to go from this....SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1FROM dbo.TEST INNER JOINdbo.LU_SEX ON dbo.TEST.SEX_DP1 =dbo.LU_SEX.SEX_DECTo this...UPDATE dbo.TEST.SEX_DP1SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODEFROM dbo.LU_SEX INNER JOINdbo.TEST ON dbo.LU_SEX.SEX_DEC =dbo.TEST.SEX_DP1Maybe I need a good book on this?Thanks,Charles

View Replies !   View Related
Create A Table From Select Queries.

I wanted to know a query which will create a final result table from a combination of select queries.

The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.

null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.

Thanks in advance.

View Replies !   View Related
Dynamic Table Names In Queries
Is there a way to dynamically build a table name for a query without making the entire query a variable and using EXEC? The query will be against a remote server and I need to construct the full table name from a variable server name and variable database (the table name itself will be constant).
Thanks in advance.

View Replies !   View Related
Multi-table Queries For M:N Situations
Hi friends,

when I write multi-table queries which involve two tables which are joined via a bridging table (M:N),

do I just join the tables or do I have to reference the bridging table as well in the queries?


View Replies !   View Related
Three Queries Of One Table As One Stored Procedure?

I currently have three queries running seperately which I'd like to join up..

However I'm sure it can be done..

Here goes:

I have one table which lists payments made (it stores a PaymentID from another table, a payment amount and an invoiceID)

Therefore there can be several records with the same PaymentID referenceing different invoices (i.e a user paying off several invoices with one payment)

I have one query which lists all of the invoices paid against one payment.

SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID = xxx AND PayType <> 'Credit'

I then have a second query which is ran against each individual invoice which shows the other payments which have been made against this invoice already

SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID<> xxx AND PayType <> 'Credit' AND InvoiceID = XXX

and finalI have one query which lists the credits
SELECT PaymentID, InvoiceID, PaymentAmount WHERE PayType= 'Credit' AND InvoiceID = XXX

All of the above lets me see an payment, which invoices have been paid against that payment.. and then for each invoice, any other payment which were made beforehand, and finally any credits against that invoice.

I run these from an ASP page in a loop which is pretty inefficient way of doing it.

I would much prefer to amalgamate the three queries above so I could see what I was paying now, what had already been paid and what was credited against each invoice from a PaymentID.. all in query.

Is that possible?


View Replies !   View Related
Large Table, Really Slow Queries
I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

count(dt_date) as Searches

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

View Replies !   View Related
SP To Add Fields To Table
I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type.
Can someone suggest how the sproc might look? Here is what I have so far.
CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO
Thank you,

View Replies !   View Related
How To Append Multiple Queries To A Temp Table?
Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.

Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?

Select * From TableA
Select * From TableB
<---output result to temporary table here--->

Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?

The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.

Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB

Thanks in advance.

View Replies !   View Related
Lock Table For Queries With Query WAITFOR
Hi all.

How can I lock table with longtime query?

View Replies !   View Related
Dynamic Table Name And Fields
I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName.  Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name.  So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop  that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email

View Replies !   View Related
Access To Fields In A Table
I am trying to access the fields in a table using Visual Basic in Web Developer .NET

View Replies !   View Related
Table Fields Name Query
Hello all,

How can i write a sql statement that can give me the name of the fileds in a specified table.

Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.

any help.

View Replies !   View Related

Copyright 2005-08, All rights reserved