Post has attachment

Post has attachment
Enrol for expert level instructor-led #SQL and #TSQL server #onlinetraining offered by +Glory IT Technologies Pvt. Ltd.

Structured Query Language is a query language used to operate on sets. It is used by almost all relational database management systems: #SQLServer, Oracle, #MySQL, #PostgreSQL, #DB2, #Informix, etc.

T-SQL is a proprietary procedural language from Sybase and Microsoft that add several features to the SQL including transaction control, row processing, exception and error handling, and declared variables. #Microsoft's SQL Server and Sybase's SQL server support T-SQL statements.

To know more about SQL & T-SQL, visit http://www.gloryittechnologies.com/Microsoft-Technologies-Online-Training.html
Photo

Hi. I'm trying to figure out the right way to do this, and I'm a bit stumped. If anyone can help me I'd appreciate it.

I have a hierarchal tree of data with ParentIDs.
I want to make a new copy of the tree, with new IDs, and I want the items in the tree to have the same relative parents in the new tree.

so if I have records like this (Name, ID, ParentID):

Top Place, 1 , 0
Sub Place 1, 2 , 1
Sub Place 2, 3 , 1
Sub Sub Place 1, 4 , 2

I want my copied tree to loo like this:
Top Place, 5 , 0
Sub Place 1, 6 , 5
Sub Place 2, 7 , 5
Sub Sub Place 1, 8 , 6

I'm not sure what the best algorithm to do this would be. Any ideas?
Thanks in advance.

Hi.  I have a database wherein I need to apply a new naming convention to a lot of columns in various tables.  These are all foreign key references to other tables.  Currently if I have two tables 
REF_TABLE_A
REF_TABLE_B
XRF_REF_TABLE_A_REF_TABLE_B

And TABLE_B has a reference to TABLE_A as follows:
TABLE_A (
ID int,
Name varchar(20)
)

TABLE_B (
  ID int,
  Name varchar(20),
 TABLE_AID int)

And the Cross reference table is like this:

XRF_REF_TABLE_A_REF_TABLE_B (
ID int,
Name varchar(20),
TABLE_AID int,
TABLE_BID int)

What I need to do is go through the database and change the foreign key names form

TABLE_AID 

to 

REF_TABLE_AID

so that the full table name is in the column name.  I want to do this in order to create a set of generic functions for various purposes and this requires that I be able to query the columns in order to find the table names that the column refers to.   I realize that this could be done as well through a query to the system tables, but I would like to have my database as neat and clear as possible and so I'd like to make this change for that reason as well.

That said - what I'd like to find out is how to query the database and find the columns that do not have the correct table name and update those names so that they are correct.  Since all of the foreign key columns follow the convention of having "ID" at the end of the column name,  at this point I can query the database and return all of the incorrectly named columns as follows:


SELECT 
   TABLE_NAME
  ,COLUMN_NAME
  ,LEN(COLUMN_NAME)-2 AS 'NAMEIND'
  ,SUBSTRING(COLUMN_NAME, 1, LEN(COLUMN_NAME) -2) AS 'P_NAME'
  ,'REF_' + SUBSTRING(COLUMN_NAME, 1, LEN(COLUMN_NAME) -2) AS 'T_NAME'
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  RIGHT(COLUMN_NAME,2) = 'ID'
  AND COLUMN_NAME <> 'ID'
  AND LEFT(TABLE_NAME, 3) <> 'SYS'
--  AND SUBSTRING(COLUMN_NAME,4,1) = '_'
  AND SUBSTRING(COLUMN_NAME,4,1) <> '_'
  AND COLUMN_NAME NOT IN 
    (
     'Elthos_ODS_Admin_User_ID',
'TransactionId',
'GM_PlayerID',
'GamesmasterID',
'ObjectID',
'BirthPlaceID',
'ParentMotherID',
'ParentFatherID',
'BirthElkronID',
'IsHumanoid',
'MAJOR_ARCANUM_ID',
'ParentTransactionId',
'RecurProfileId'
)
  AND (Left(TABLE_NAME,4) = 'REF_' OR left(TABLE_NAME, 4) = 'XRF_')
  --and EXISTS (SELECT * FROM  INFORMATION_SCHEMA.COLUMNS 
  --            WHERE TABLE_NAME = 'REF_' + SUBSTRING(COLUMN_NAME, 1, LEN(COLUMN_NAME) -2))
ORDER BY 
  TABLE_NAME ASC; 

This returns the correct columns.  From here what I would like to do is assign new names to the offending columns, and to do so what I would like to do is find the table name to which the column is associated and at the 'XRF_' or 'REF_' to the left side of the column name and update the table with the revised column name.

Can anyone help me with information or guidance on this?  Thanks!

Post has attachment
Hi - I posted this to stackoverflow and defined the problem I'm working on there.  I thought I might post a link to the question here as well (it would be rather difficult to write it up conveniently in a google+ post).  If anyone knows the answer to this, I'd be very appreciative.  Thanks.

Post has attachment
Sorting of data is not important but when it comes in T-SQL then it's a source of lot of confusion... 

Post has attachment
Check one of the most common feature of queries.... 

Post has attachment
Check one of the most common feature of queries.... 

Post has attachment
Wait while more posts are being loaded