how to enter the records from one table to another table at the same time

[developerit]

hi iam using sql server 2000 iam having two tables with same fields my first tableA contains the fields Orderid int primary key identity(automatically increment) productname nvarchar price nvarchar quantity nvarchar total nvarchar my second tableB contains the fields Orderid int (no primary key) productname nvarchar price nvarchar quantity nvarchar total nvarchar from asp.net iam entering multiple values at a time in tableA, at the same time i want to insert into tableB can you give the querry which helps me

[Dylan Morley]

A stored procedure that performs the operation should do it. Or you could put a trigger on TableA if you liked. I'd question your datatypes for fields like price, quantity, total etc. Shouldn't be using strings for what looks like numeric information

CREATE PROCEDURE CreateOrderDetails

(
	@ProductName			NVARCHAR(50), 
	@Price				NVARCHAR(10), 
	@Quantity			NVARCHAR(10), 
	@Total				NVARCHAR(10)
)

AS

SET NOCOUNT ON

DECLARE @MyRecordId INT

INSERT INTO
TableA (productname, price, quantity, total)
VALUES
(@ProductName, @Price, @Quantity, @Total)

--Get the ID of the record we just created
SET @MyRecordId = SCOPE_IDENTITY()

INSERT INTO
TableB (Orderid, productname, price, quantity, total)
SELECT
Orderid, productname, price, quantity, total
FROM
TableA
WHERE
Orderid = @MyRecordId

RETURN @@ERROR
SET NOCOUNT OFF

[Mycroft Holmes]

I'm curious, what is the logic for creating such a data structure, why are you storing data twice. And yes you really should change you data types away from the default, it will cause you no end of problems.

Never underestimate the power of human stupidity RAH

[Sandesh M Patil]

Hey by using trigger u can do this. Trigger can used to implemnt business rules

[Jon_Boy]

I wouldn't do this since triggers can slow down the DB. Stored procedure or sep. calls is better. If it needs to be all or nothing and assuming the inserts are single row, wrap in a transaction.

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

[dan sh]

You can make your stored procedure save same data in both the tables (two insert statements). BTW, why are you doing this? Don't you think it is weird?