Re: Getting Children of Parent Within a table - Database Discussion Boards

copy a column from one table to another [modified]

rzvme14-Jan-07 2:18

14-Jan-07 2:18

How can i copy a column from one table to another (data and schema) at runtime in vs 2005?

the data and schema are read from xml files

-- modified at 8:44 Sunday 14th January, 2007

rzvme

Re: copy a column from one table to another

Elina Blank14-Jan-07 10:25

Elina Blank

14-Jan-07 10:25

Write an sp, that will do that and call it from your code.

The sp (for SQL server) will look something like this:

INSERT INTO [InsertDB].[dbo].[InsertTable]<br />
           ( [ColumnName1],[ColumnName2] )<br />
   SELECT  [ColumnName1],[ColumnName2]<br />
FROM [SourceDB].[dbo].[SourceTable]

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

oracle9i

pradeep kulkarni12-Jan-07 22:02

pradeep kulkarni

12-Jan-07 22:02

how to create table in SQL, give one example

pradeep kulkarni

Re: oracle9i

ChandraRam13-Jan-07 1:20

ChandraRam

13-Jan-07 1:20

pra47 wrote:
how to create table in SQL, give one example

Look up the SQL syntax for the CREATE TABLE statement in Google or in a reference book. You will need to supply, at a minimum, the field name and field type for each field that you want the table to contain.

HTH

Getting Children of Parent Within a table

May Thu san12-Jan-07 19:56

May Thu san

12-Jan-07 19:56

I have a table Named 'Roles' which contains 'ID' and 'ParentID' fields.

Some Sample Data is

ID ,Name ,ParentID
1 ,Admin ,0
2 ,Manager ,1
3 ,Front Office User ,1
4 ,Supervisor ,2
5 ,Inventory User ,0
6 ,Human Resource User ,4

I would like to extract data of Parent and all children.

SELECT b.[ID] as 'Parent', a.[ID] as 'Role'
FROM Roles a
INNER JOIN Roles b
ON a.ParentID = b.[ID] and a.ParentID=1

which can return only.

ID ,ParentID
1 ,2
1 ,3

My wanted form is

ParentID ,ID
1 ,2
1 ,3
2 ,4
4 ,6

Like an organization tree, the top parent level can access all lower level nodes.
Sql 2005 support that feature.
How can I access them in Sql2000 recursive function or store procedure?

Thanks

May Thu San.

Re: Getting Children of Parent Within a table

WoutL12-Jan-07 22:47

WoutL

12-Jan-07 22:47

I think you made a little mistake in your query. This should work:

SELECT b.[ID] as 'Parent', a.[ID] as 'Role'<br />
FROM Roles a<br />
INNER JOIN Roles b<br />
ON a.ParentID = b.[ID]

Wout Louwers

Re: Getting Children of Parent Within a table

May Thu san12-Jan-07 22:58

May Thu san

12-Jan-07 22:58

Sorry, My Query is

SELECT b.[ID] as 'Parent', a.[ID] as 'Role'
FROM Roles a
INNER JOIN Roles b
ON a.[ParentID] = b.[ID] and a.ParentID=1

Some Sample Data is

ID ,Name ,ParentID
1 ,Admin ,0
2 ,Manager ,1
3 ,Front Office User ,1
4 ,Supervisor ,2
5 ,Inventory User ,0
6 ,Human Resource User ,4

The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc.

How can I get all children?

Re: Getting Children of Parent Within a table

Colin Angus Mackay13-Jan-07 9:27

Colin Angus Mackay

13-Jan-07 9:27

May Thu san wrote:
The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc.

How can I get all children?

by removing the "and a.ParentID=1" from the query.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Re: Getting Children of Parent Within a table

WoutL13-Jan-07 22:42

WoutL

13-Jan-07 22:42

I think I understand what you want now. But this cannot be easily solved whit the ParentID. This[^] article should help you.

Wout Louwers

how to get the records in between dates

Rmesh12-Jan-07 19:36

Rmesh

12-Jan-07 19:36

how to write the query to get the records in between dates

Re: how to get the records in between dates

May Thu san12-Jan-07 20:25

May Thu san

12-Jan-07 20:25

SELECT * FROM TableName WHERE DateFieldName BETWEEN '1-JAN-07' AND '31-JAN-07'

embedded sql

veeresham12-Jan-07 19:06

veeresham

12-Jan-07 19:06

how to use sql in host languages like c / c++ Sleepy | :zzz:

Re: embedded sql

Colin Angus Mackay13-Jan-07 9:25

Colin Angus Mackay

13-Jan-07 9:25

Have you had a look at LINQ?

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

how to display all tables from any single database in sql server 7.0?

sathyan_829412-Jan-07 19:01

sathyan_8294

12-Jan-07 19:01

how to display all tables from any single database in sql server 7.0.

Re: how to display all tables from any single database in sql server 7.0?

Rmesh12-Jan-07 22:14

Rmesh

12-Jan-07 22:14

select * from Sysdatabases where xtype='u'

Lock Types

Rahithi12-Jan-07 18:50

Rahithi

12-Jan-07 18:50

hi,
what are the lock types available with "recordsets" and How different are they from “datasets”
Thanks,
Rahi

If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "

SQL Query help

Irshad Ahmed12-Jan-07 4:03

Irshad Ahmed

12-Jan-07 4:03

Please, help me in solving this query

I have a table some thing like this

Sens_Name T2_Value Q_Value CMI_Value
---------------------------------------------------
Sens 1 12 70 80
Sesn 2 13 71 81

I want to query the above table so that I get an output in this way

Sens_Name Stat_Value Stat_Type
--------------------------------------------------------------
Sens 1 12 T2_Value
Sens 1 70 Q_Value
Sens 1 80 CMI_Value
Sens 2 13 T2_Value
Sens 2 71 Q_Value
Sens 2 81 CMI_Value

Thanks in advance
Irsh

Re: SQL Query help

Michael Potter12-Jan-07 4:20

Michael Potter

12-Jan-07 4:20

You can use a UNION clause:

SELECT 
    Sens_Name,
    T2_value AS Stat_Value,
    'T2_Value' AS Stat_Type
FROM
    SensTable

UNION

SELECT 
    Sens_Name,
    Q_value AS Stat_Value,
    'Q_Value' AS Stat_Type
FROM
    SensTable

UNION

SELECT 
    Sens_Name,
    CMI_Value AS Stat_Value,
    'CMI_Value' AS Stat_Type
FROM
    SensTable

ORDER BY 
    Sens_Name,
    Stat_Value

Re: SQL Query help

Irshad Ahmed23-Jan-07 16:51

Irshad Ahmed

23-Jan-07 16:51

Thanks Mike

aspnet_regsql - Unable to connect

stuf9911-Jan-07 21:30

stuf99

11-Jan-07 21:30

Im trying to establish an ASPState database with the aspnet_regsql command. I receive an error informing me that the wizard was unable to connect to the sql server. I have an express server installed and have no problems connecting to it otherwise. On msdn it says that

The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true).

Im not sure what that means?

I have also made the preparations with the T-SQL commands so I could enable the Agent XPs SQL Server option with the following commands

EXECUTE sp_configure 'show advanced options', 1<br />
RECONFIGURE WITH OVERRIDE<br />
GO<br />
<br />
EXECUTE sp_configure 'Agent XPs', 1<br />
RECONFIGURE WITH OVERRIDE<br />
GO<br />
<br />
EXECUTE sp_configure 'show advanced options', 0<br />
RECONFIGURE WITH OVERRIDE<br />
GO

Can anyone help?

How to make access to SQL 2000 from WAN only for one login?

yarns11-Jan-07 20:58

yarns

11-Jan-07 20:58

Hi,

i created user with access to read only database. Its possible to make that only this one user (rest of users from LAN only) will have access to database from WAN?

Greetings

Re: How to make access to SQL 2000 from WAN only for one login?

Mark J. Miller12-Jan-07 8:28

Mark J. Miller

12-Jan-07 8:28

If you're asking if SQL Server can control this, the answer is no. If a user can connect to SQL Server and present the correct credentials, they will be authenticated. It doesn't matter where they connect from.

Are you using SQL Server authentication or Windows Authentication? If you're using SQL Server authentication then you have no control over this - which is why Windows Authentication is the recommended best practice. If you're using Windows Authentication, then you should be able to control this by configuring which WAN accounts have access to your LAN and which of those WAN accounts which have access may also access SQL Server.

How to insert data into table from another existing table in Oracle 9i

BawaSingh11-Jan-07 20:07

BawaSingh

11-Jan-07 20:07

Kindly help me out regarding this. pls tell me the sql query for this..

D'Oh! | :doh: