INSERT sqlserver

[prodigy]

I have created 2 tables in sql server 2000 and they have Relation between them:

(1) tblCustomer
columns: custid (PK); custname

(2) tblOrder
columns: orderid; customer_id (FK); order

I want to write the sql statement (INSERT) to insert a record with all fields.
I dont want to inculde the customerid (FK) in the sql statement (because the tblOrder.customerid should get the matching value of tblCustomer.custid otherwise how will the relationship between the tables exist). I dont know if I was able to convey the problem. If someone understands pls provide me with the resolution.

 

[Matt Greatorex]

The following assumes that all variables are text, since no calculations are going to be carried out with any field values.

One insert runs immediately after the other, so the Order table will link to the Customer table, on the Customer's ID number.

Dim str_SQL as String
Dim str_CustID as String
Dim str_CustName as String
Dim str_OrderID as String
Dim str_Order as String

'**
[I]Set values here for each of the above variables, with the exception of str_SQL[/I]
'**

str_SQL = "INSERT INTO tblCustomer (CustID, CustName) VALUES ('" & str_CustID & "','" & str_CustName & "');"
DoCmd.RunSQL  str_SQL

str_SQL = "INSERT INTO tblOrder (OrderID, CUstomerID, Order VALUES('" & str_OrderID & "','" & str_CustID & "','" & str_Order & "');"
DoCmd.RunSQL  str_SQL

Hope that makes sense.

 

[prodigy]

sql statement in sql query analyzer

Hi Matt Greatorex,
Thanks for your prompt reply.Though I have not tried ur suggestion but I believe it will work. However, what I want is to write the sql statement in the SQL QUERY ANALYZER that comes with sql-server 2000.
If you have any suggestion.

 

[Matt Greatorex]

Alas, never used it.
Good luck with it, though.

 

[SQL_Hell]

Hi Prodigy

before I start

Is the primary key in table customer an autonumber?

 

[prodigy]

reply to SQL_HELL

Hi,
The primary key in the cust_tbl is not an autonumber field. (e.g. It is a defined no based on account_no). If it helps you. Thanks.

 

[SQL_Hell]

INSERT INTO tblCustomer (CustID, CustName) VALUES ('234','Mr superman')
GO
INSERT INTO tblOrder (OrderID, CUstomerID, Order) VALUES('300','234','PAIR OF RED PANTS')
GO

 

[prodigy]

thanks SQL_HELL

Hi SQL_HELL,
Thanks for the reply. Will try as suggested.