Data insertion in 2 table

Question

I have 2 tables:

Table T1

Id Test  Question
-----------------
1  Test1 Q
2  Test2 Q
3  Test3 Q

Table T2

ID T1_ID Ans
------------
1    1   A1
2    1   A2
3    2   B1
4    2   B2
5    2   B3
6    3   C1
7    3   C2

I am inserting T1 values using select statement with different question value

Insert into T1 (Test, Question) 
Select Test, 'P' From T1

Similarly I want to copy data of T2, T1.Id should be newly generated id's from T1 table for question p so if T2 has 7 records for Question Q then P also should have 7 records but newly generated id of T1 table of Question P.

Insert into T2 (T1_ID, Ans)
Select (T1_ID where Question=P), Ans From T2

Output

Table T1

Id Test  Question
-----------------
1  Test1 Q
2  Test2 Q
3  Test3 Q
4  Test1 P
5  Test2 P
6  Test3 P

Table T2

ID T1_ID Ans
------------
1    1   A1
2    1   A2
3    2   B1
4    2   B2
5    2   B3
6    3   C1
7    3   C2
8    4   A1
9    4   A1
10   5   B1
11   5   B2
12   5   B3
13   6   C1
14   6   C2

That wasn't what he meant. See my edit. See also https://stackoverflow.com/help/formatting — ADyson, Oct 10 '19 at 09:30
no worries. Anyway, this should help you with your problem: https://dba.stackexchange.com/a/55478/189204 — ADyson, Oct 10 '19 at 09:33
@ADyson from T1 table id is autogenerated so i am unable to store that in table variable, can you help me with an example. — prashanat chaudhari, Oct 10 '19 at 10:34
@prashanatchaudhari the whole point of that idea is precisely so you can access the set of auto-generated (i.e. "identity") IDs created by your first insert statement and put them in a table variable. Did you actually read the Microsoft article linked from there? The first [example](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?redirectedfrom=MSDN&view=sql-server-2017#examples) it lists is doing exactly what you're asking for. Please ensure you study any links fully when people provide them :-) — ADyson, Oct 10 '19 at 11:23
P.S. The new answer below has made an explicit version using your tables anyhow. — ADyson, Oct 10 '19 at 11:27
@ADyson i did try that, However data inserted in T2 Table was not as expected. In T2 table it should copy only 7 records but total rows affected was more than that. — prashanat chaudhari, Oct 10 '19 at 11:37
ok. So please specify precisely the output you want then. All you've said you want is a "copy" of t2. The query below gives you a copy - one copy for each new ID. That's what a cross join does. If you want something different please edit your question to give the specific desired result. — ADyson, Oct 10 '19 at 12:07
@ADyson It Should insert only 7 records, For example - In T1 table when we copied data Suppose For Id=1 it inserted as Id 4 Test1 P, in T2 table T1.ID=1 has 2 records so T1.ID=4 should also have 2 records. — prashanat chaudhari, Oct 10 '19 at 12:32
I think I get you but, for 100% clarity, as requested previously, please **edit your question** to give the specific desired result, in a table. Thanks. — ADyson, Oct 10 '19 at 12:33
@ADyson add desired result in Question, Hope that will clear your doubts. — prashanat chaudhari, Oct 10 '19 at 12:43
So, you want to "clone" a couple of questions and their answers? Take a look at [my solution for this situation](https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage) — Zohar Peled, Oct 10 '19 at 12:45
@ZoharPeled Yes I want cloning of questions and answers. I will try suggested approach. — prashanat chaudhari, Oct 10 '19 at 13:20

score 1 · Answer 1 · answered Oct 10 '19 at 11:09

1

In SQL Server, one method uses the output clause:

declare @ids table (id int);

insert into T1 (Test, Question)
    output inserted.id into @ids
    select Test, 'P' ;

insert into T2 (T1_ID, Ans)
    select ip.id, t2.Ans
    from t2 cross join
         @ids ip;

Or, just search for it:

insert into T2 (T1_ID, Ans)
    select t1.id, t2.Ans
    from t2 join
         t1
         on t1.question = 'P';

answered Oct 10 '19 at 11:09

Gordon Linoff

1,242,037
58
646
786

Hello Gordon, i tried this, However data inserted in T2 Table was not as expected. In T2 table it should copy/insert only 7 records but total rows affected was more than that. – prashanat chaudhari Oct 10 '19 at 11:40
@prashanatchaudhari . . . Table2 has 14 records in your example. You have no filtering in your sample query, but presumably you want a `where` clause for some filtering. – Gordon Linoff Oct 10 '19 at 13:24

ADyson · Answer 2 · 2019-10-10T13:32:09.843

0

I found a way to do what you want. It feels a bit hacky but I can't see a better way (although someone may come along and contradict me). You should probably wrap all this in a transaction to make sure it functions correctly without interference.

It works by simply counting the number of inserted rows, and assuming that the IDs generated are sequential beginning at the next available number after the highest existing one. (This may fail in cases where rows have previously been deleted from the end of the table, for example). I couldn't see any other way to create a meaningful relationship between t2 and the list of IDs.

declare @ids table (id int);

insert into t1 (test, question)
    output inserted.id into @ids
    select test, 'P' from t1;

declare @cnt int;
select @cnt = count(id) from @ids;
insert into t2 (t1_id, ans)
    select i.id, t2.ans
    from t2
    left join @ids i on i.id = (t2.t1_id + @cnt)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=305347d11d2c18f6a4ce3bc3d4b516f3

edited Oct 10 '19 at 13:32

answered Oct 10 '19 at 13:08

ADyson

57,178
14
51
63

"assuming that the IDs generated are sequential beginning at the next available number after the highest existing one." That's quite a risky assumption. Gaps in identity columns can be generated in multiple situations, not only by deleting rows. Read [SQL Server’s identity column misconceptions](https://zoharpeled.wordpress.com/2019/10/06/sql-servers-identity-column-misconceptions/) for more details. – Zohar Peled Oct 10 '19 at 13:19
@ZoharPeled I know...I explicitly caveated that already, although yes there is more than one situation that it can occur...deletion is just an obvious example. I see you posted a link to a generic approach which is no doubt more reliable. Is it sufficiently similar to qualify as a direct duplicate, in your opinion? – ADyson Oct 10 '19 at 13:31

score 0 · Accepted Answer · answered Oct 11 '19 at 11:41

What work for me is

{

DECLARE @LoopCounter INT , @MaxCounter INT,@DestinationQ NVARCHAR(100),@SourceQ NVARCHAR(100)

SET @SourceQ='Q'
SET @DestinationQ ='P'

SELECT @LoopCounter = min(Id) , @MaxCounter = max(Id) 
FROM T1 where Question=@SourceQ 
WHILE(@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxCounter)   
    BEGIN
        declare @NewlyGeneratedID table (New_ID int)
        Insert INTO T1 (Test, Question)
        OutPut inserted.Id into @NewlyGeneratedID
        SELECT Test, @DestinationQ From T1 WHERE Question=@SourceQ and Id = @LoopCounter;       
        insert into T2 (T1_ID, Ans)
        Select (Select Max(New_ID) from @NewlyGeneratedID),Ans From T2 Where T1_ID=@LoopCounter
        SELECT @LoopCounter  = min(Id) FROM T1 WHERE Id > @LoopCounter and Question=@SourceQ    
    END

}

Thank you all for your time.

Data insertion in 2 table

3 Answers3