Database

4-Oct-07 1:34

Try something like:

insert into cityzone
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south'

Query required

veereshIndia3-Oct-07 23:06

3-Oct-07 23:06

Hi All,

Iam having two tables

table1

1.Rno Primary key
2.name
3.empid foreign key of table2

table2

1. empid primary key
2.address
3.Rno foreign key of table1

Here Rno and empid are auto generated. Now i want to insert data into first table i need sql query for this.Please help me on this

i want to join this group

John-ph3-Oct-07 23:33

3-Oct-07 23:33

veereshIndia wrote:
i want to insert data into first table i need sql query for this

INSERT INTO tblEmployee(
     EmpolyeeID,
     EmployeeName)
VALUES(
     @EmpolyeeID,
     @EmployeeName)

Regards
J O H N

"Even eagles need a push." David McNally

veereshIndia3-Oct-07 23:35

3-Oct-07 23:35

Hi,

Please see my question once again.

Regard's
veeresh

i want to join this group

John-ph4-Oct-07 0:05

4-Oct-07 0:05

To Insert Into First Table

INSERT INTO tblEmp1(
                         EmpID,
                         EmpName
                      )
          VALUES(
                         @EmpID,
                         @EmpName
                       )

To Insert Into Second Table

<code>INSERT INTO tblEmp2(
                        RowID,
                        EmpAddress,
                     )
         VALUES(
                        @RowID,
                        @EmpAddress
                       )</code>

Regards
J O H N

"Even eagles need a push." David McNally

Krish - KP4-Oct-07 0:30

Krish - KP

4-Oct-07 0:30

both insert statements results in violation of foreign key constraint

Regards
KP

John-ph4-Oct-07 0:41

4-Oct-07 0:41

Table Relationship's are Violating the Normalization Rule.

Regards
J O H N

"Even eagles need a push." David McNally

Krish - KP4-Oct-07 0:28

Krish - KP

4-Oct-07 0:28

disable constraint then a dummy master data.

use the same while inserting into transaction table.

Regards
KP

John-ph4-Oct-07 0:38

4-Oct-07 0:38

Inserting Into these table's are possible only If one of the table's Foreign Key column Allows Null Value.

Regards
J O H N

"Even eagles need a push." David McNally

Paddy Boyd4-Oct-07 0:35

4-Oct-07 0:35

You have two foreign keys, each referencing the primary key of the other table. Why do you have this circular reference?

Makes inserting records a little awkward...

John-ph4-Oct-07 0:44

4-Oct-07 0:44

You are right.

Regards
J O H N

"Even eagles need a push." David McNally

Paddy Boyd4-Oct-07 0:46

4-Oct-07 0:46

I know

veereshIndia4-Oct-07 1:09

4-Oct-07 1:09

Hi,

Thanks for giving answer.But my application requires this things.Please help me
on this.

i want to join this group

Paddy Boyd4-Oct-07 1:14

4-Oct-07 1:14

I can't understand an application that would require this design, it seems fairly fundamentally flawed.

Think about it - if your foreign key fields can't accept nulls and you are trying to insert a record:

1. Try to insert a record into A, fails because corresponding field does not exist in B.

2. Try to insert a corresponding record into B, fails because initial record does not exist in A.

3. Keep trying because it doesn't work.

It seems like you are trying to enforce a one to one relationship between these two tables - why not combine them into a single table?

veereshIndia4-Oct-07 1:27

4-Oct-07 1:27

Hi,

I will try to combine into only one table.Thanks a lot.

Veeresh

i want to join this group

karoitay4-Oct-07 0:52

karoitay

4-Oct-07 0:52

It looks like your purpose is to have roles list ant employees list and you need some how to assign roles to employees.

If this is the case - I would have done this with three Tables.
TableEmps -- empId, empName (PK - empId)
TableRoles -- roleId, RoleName (PK - roleId)
TableRoleToEmp -- roleId, empId (FK1 roleId ref - TableRoles.roleId, FK2 empId ref - TableEmps.empId)

Remote Connection MSSQL

confusedme3-Oct-07 19:52

confusedme

3-Oct-07 19:52

Hi..

I have been assigned to make a database in some remote machine.The machine has MSSQL 2005.I need to connect to that remote machine's sql server via my pc using my SQL Management express.I do have that computer's IP address(say,123.456.789.000),and the username and password.But when i try to connect to the server ,I get the following error:

Cannot connect to 123.456.789.000\sqlexpress

An error has occured while establishing a connection to the server.When connecting to SQL server 2005,this failure may be caused by the fact that unser the default settings SQL server does not allow remote connections,(provider:SQL Network Interfaces,error:26-Error Locating Server/Instance specified)(Microsoft SQL Server)

I am a new programmer and dont know much.But i guess the problem is not in my server but in that server to which i am trying to connect.I guess that server has some instance created or it does not allow remote connections.But i am not sure.Is it so?Or is anything wrong in my server.Any ideas?

Thanks

confused

Anilkumar K V3-Oct-07 20:18

Anilkumar K V

3-Oct-07 20:18

I too had same issue like this

I installed SQL Server SE, on an XP machine. How other users of same domain can access SQL server using SQL Server Express Edn.
I did create new logins for user, windows authentication. But whenever user try to connect it show message like

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance Specified).

How to get rid of this issue. Do i need to set/change in WInodws user accounts?

Joe3-Oct-07 20:21

Joe

3-Oct-07 20:21

Does this issue occur with the SqlServer service started on the remote computer?

Re: Remote Connection MSSQL [modified]

Anilkumar K V3-Oct-07 22:46

Anilkumar K V

3-Oct-07 22:46

I could login from the local machine. When when other user try to login it show error.
It seems that Windows Authentication issues are there.

What all things to do in the SQL Ser. Standard Edition ( XP ) to enable remote login
( like user permission, adding new login , etc etc )

Marek Grzenkowicz3-Oct-07 22:13

Marek Grzenkowicz

3-Oct-07 22:13

Contact the DBA responsible for the server and ask them to use SQL Server Surface Area Configuration tool to check whether SQL Server is configured to allow remote connections.

Anilkumar K V3-Oct-07 23:31

Anilkumar K V

3-Oct-07 23:31

Its enabled

Marek Grzenkowicz3-Oct-07 23:47

Marek Grzenkowicz

3-Oct-07 23:47

Is SQL Browser service running?