June 12, 2012 at 6:33 am
Hi,
i have two tables and i want to insert data in these two table with one click
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
COMPANY NVARCHAR(50),
CAT NVARCHAR(50)
)
DROP TABLE #mytable2
CREATE TABLE #mytable2
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Contact NVARCHAR(50),
mytable1ID INT
)
i want to insert data in both with one query....
Note:mytable1ID is FK in mytable2
Note: I know i can do like this
SET IDENTITY_INSERT #mytable1 ON
INSERT INTO #mytable1
(ID, COMPANY, CAT)
SELECT '4','PIZZA HUT SINGAPORE PTDLTD','PIZZA'
SET IDENTITY_INSERT #mytable1 OFF
Select @maxId=Max(Id) from mytable1
SET IDENTITY_INSERT #mytable2 ON
INSERT INTO #mytable2
(ID, Contact, mytable1ID)
SELECT '1','PIZZA HUT SINGAPORE PTDLTD',@maxId
SET IDENTITY_INSERT #mytable2 OFF
i do not like this approach as of data integrity ..might be at process time maxId changed
June 12, 2012 at 6:50 am
The quick DYI answer would be:
1. No identity on second table
2. Do all the data modifications in a single transaction
3. Insert in the first table
4. Retrieve SCOPE_IDENTITY to get the inserted value (to a variable)
5. Use this variable in the second insert
June 12, 2012 at 6:55 am
You could set up an insert trigger on the first table.
But what you probably really want is to insert into both tables within a single transaction using separate insert statements.
June 12, 2012 at 7:00 am
Single transaction mean with in a store procedure?
What if i do not have Identity column id first table?If i have only uniqueidentifier....?
What if i create a time stamp in first table and get max of that time stamp?
max time stamp will be the new value....Is It?
June 12, 2012 at 7:13 am
You can try this:
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
COMPANY NVARCHAR(50),
CAT NVARCHAR(50))
IF OBJECT_ID('TempDB..#mytable2','U') IS NOT NULL
DROP TABLE #mytable2
CREATE TABLE #mytable2
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Contact NVARCHAR(50),
mytable1ID INT)
SET IDENTITY_INSERT #mytable1 ON
SET IDENTITY_INSERT #mytable2 OFF
INSERT INTO #mytable1 (ID, COMPANY, CAT)
OUTPUT '1', Inserted.Company, Inserted.ID
INTO #mytable2
SELECT '4','PIZZA HUT SINGAPORE PTDLTD','PIZZA'
--SET IDENTITY_INSERT #mytable1 ON
--SET IDENTITY_INSERT #mytable2 ON
SELECT * FROM #MyTable1
SELECT * FROM #MyTable2
DROP TABLE #MyTable1, #MyTable2
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 12, 2012 at 7:21 am
The only issue is for data integrity....i dnt want mixing of data...Is Inserted.ID is good option OR SCOPE_IDENTITY?which one i should use?
June 12, 2012 at 7:23 am
Methew (6/12/2012)
The only issue is for data integrity....i dnt want mixing of data...Is Inserted.ID is good option OR SCOPE_IDENTITY?which one i should use?
Well, it's not like Inserted.ID is going to grab an ID value from some other INSERT. I'd say its a pretty safe bet.
But others with more experience may know better.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 12, 2012 at 7:28 am
dwain.c (6/12/2012)
Methew (6/12/2012)
The only issue is for data integrity....i dnt want mixing of data...Is Inserted.ID is good option OR SCOPE_IDENTITY?which one i should use?Well, it's not like Inserted.ID is going to grab an ID value from some other INSERT. I'd say its a pretty safe bet.
But others with more experience may know better.
I think SCOPE_IDENTITY is good only if we have identity column with PK.if we have only uniqueidentifier as PK then we can not use SCOPE_IDENTITY.
THen the only option is Inserted.ID as you suggested in your query
June 12, 2012 at 9:47 am
Methew (6/12/2012)
dwain.c (6/12/2012)
Methew (6/12/2012)
The only issue is for data integrity....i dnt want mixing of data...Is Inserted.ID is good option OR SCOPE_IDENTITY?which one i should use?Well, it's not like Inserted.ID is going to grab an ID value from some other INSERT. I'd say its a pretty safe bet.
But others with more experience may know better.
I think SCOPE_IDENTITY is good only if we have identity column with PK.if we have only uniqueidentifier as PK then we can not use SCOPE_IDENTITY.
THen the only option is Inserted.ID as you suggested in your query
Yes you should the OUTPUT clause like Dwain suggested. SCOPE_IDENTITY will also let you down if your insert is more than 1 row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2012 at 3:09 am
Ok i used @dwain.c suggestion
i might not think about data integrity.....If yes then solved my problem
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply