CODEWITHSUNDEEP
sql-servert-sqlsql-insert
Elham Azadfar
Elham Azadfar

Reputation: 739

Inserting multiple rows in SQL?

I have WindowTable with following data :

SELECT  Id FROM WindowTable WHERE OwnerRef=12

 Id
----
25000
25001
25003
25004
25005
25006
25007
25008

I want to Insert 3 row per each WindowTable Row in ActionTable Like this :

Id    WindowsRef     ActionName     ActionName2     

-----------------------------------------------
1       25000          'Add'          'E'
2       25000          'DELETE'       'H'
3       25000          'UPDATE'       'B'  
4       25001          'ADD'          'E'
5       25001          'DELETE'       'H' 
6       25001          'Update'       'B'
.         .              .
.         .              .

ActionTable.Id is not identity column

Upvotes: 1

Views: 167

Answers (5)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Hope this helps

Test Data

;WITH cte_TestData(WindowsRef) AS
(
SELECT 25000 UNION ALL
SELECT 25001 UNION ALL
SELECT 25003 UNION ALL
SELECT 25004 UNION ALL
SELECT 25005 UNION ALL
SELECT 25006 UNION ALL
SELECT 25007 UNION ALL
SELECT 25008
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    a.WindowsRef,
    b.ActionName,
    b.ActionName2
FROM cte_TestData a
CROSS JOIN (
    SELECT 'Add' AS ActionName,'E' AS ActionName2   UNION ALL
    SELECT 'DELETE','H' UNION ALL
    SELECT 'UPDATE','B') b
ORDER BY a.WindowsRef

Against Actual Data

;WITH cte_TestData(WindowsRef) AS
(
SELECT  Id
FROM WindowTable 
WHERE OwnerRef=12
)
,cte_Action AS
(
SELECT 'Add' AS ActionName,'E' AS ActionName2   UNION ALL
SELECT 'DELETE','H' UNION ALL
SELECT 'UPDATE','B'
)
--INSERT INTO <DestinationTable> 
/* 
- Replace <DestinationTable> with Target Table Name 
- If the destination table has data the the ID has to be incremented accordingly. 
-- In that case define a variable, get MAX of that ID and add that to the below 
   auto generated ID to preserve the sequence. 
--Better yet, use Identity column as your ID Column
*/
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
    a.WindowsRef,
    b.ActionName,
    b.ActionName2
FROM cte_TestData a
CROSS JOIN cte_Action b
--WHERE NOT EXISTS (SELECT 1 FROM <DestinationTable> c WHERE c.WindowsRef = a.WindowsRef) 
/* 
Enable to ensure duplicate records are not inserted, Replace <DestinationTable> with Target Table Name 
*/
ORDER BY a.WindowsRef

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

one more approach using apply.Using sample data from Praveen ND

select *,row_number() over (order by id) as id from @WindowTable
cross apply
(
values('add','E'),
      ('delete','h'),
      ('update','b')
)b(action,action2)

Upvotes: 3

Richard
Richard

Reputation: 108995

Something like:

insert into ActionTable(WindowsRef, ActionName)
select id WindowsRef, 'Add'
  from WindowsTable
union all
select id WindowsRef, 'DELETE'
  from WindowsTable
union all
select id WindowsRef, 'UPDATE'
  from WindowsTable

(Assuming ActionTable.Id is an identity column or otherwise database generated.)

To generate id values as well

insert into ActionTable(id, WindowsRef, ActionName)
select
    (isnull(select max(id) from ActionTable, 0)
        + row_number() over (order by x.WindowsRef, x.ActionName)
    ) id,
    x.WindowsRef, x.ActionName
from (
    select id WindowsRef, 'Add' ActionName
      from WindowsTable
    union all
    select id WindowsRef, 'DELETE' ActionName
      from WindowsTable
    union all
    select id WindowsRef, 'UPDATE' ActionName
      from WindowsTable
) x

Upvotes: 4

Praveen ND
Praveen ND

Reputation: 560

This will help you to create the script for inserting to ActionTable

DECLARE @WindowTable TABLE (ID INT)
INSERT INTO @WindowTable VALUES
(25001),
(25003),
(25004),
(25005),
(25006),
(25007),
(25008)

SELECT 'INSERT INTO ActionTable (WindowsRe,ActionName) VALUES ('+ CAST(ID AS NVARCHAR(MAX))+',ADD)' FROM @WindowTable
UNION
SELECT 'INSERT INTO ActionTable (WindowsRe,ActionName) VALUES ('+ CAST(ID AS NVARCHAR(MAX))+',UPDATE)' FROM @WindowTable
UNION
SELECT 'INSERT INTO ActionTable (WindowsRe,ActionName) VALUES ('+ CAST(ID AS NVARCHAR(MAX))+',DELETE)' FROM @WindowTable

Note : Considering ID in ActionTable as IDENTITY.

IF ID in ActionTable is not an IDENTITY : Try to make use of below Query :

DECLARE @WindowTable TABLE (ID INT)
INSERT INTO @WindowTable VALUES
(25001),
(25003),
(25004),
(25005),
(25006),
(25007),
(25008)

DECLARE @id INT =1;
DECLARE @id1 INT = (SELECT COUNT(*) FROM @WindowTable)
DECLARE @id2 INT = (SELECT 2 *COUNT(*) FROM @WindowTable)


SELECT 'INSERT INTO ActionTable (ID,WindowsRe,ActionName,ActionName2) VALUES ('+CAST(ROW_NUMBER() OVER(ORDER BY @id) AS NVARCHAR(MAX))+','+ CAST(ID AS NVARCHAR(MAX))+',ADD,''E'')' FROM @WindowTable
UNION
SELECT 'INSERT INTO ActionTable (ID,WindowsRe,ActionName,ActionName2) VALUES ('+CAST(@id1+ ROW_NUMBER() OVER(ORDER BY @id1) AS NVARCHAR(MAX))+','+ CAST(ID AS NVARCHAR(MAX))+',UPDATE,''B'')' FROM @WindowTable
UNION
SELECT 'INSERT INTO ActionTable (ID,WindowsRe,ActionName,ActionName2) VALUES ('+CAST(@id2+ ROW_NUMBER() OVER(ORDER BY @id2) AS NVARCHAR(MAX))+','+ CAST(ID AS NVARCHAR(MAX))+',DELETE,''H'')' FROM @WindowTable

Upvotes: 2

oryol
oryol

Reputation: 5248

Or:

insert into ActionTable (Id, WindowsRe, ActionName, ActionName2)
select
   isnull((select max(at.Id) from ActionTable at), 0) +
   row_number() over (order by w.Id, a.Action),
   w.Id, a.Action, a.Action2
from WindowsTable w
cross join
(
  select 'Add' as Action, 'E' as Action2
  union all select 'Delete', 'H'
  union all select 'Update', 'B'
) a

UPD: Fixed misstyping. Thanks @Hua_Trung for comment

UPD2: Added ActionTable.Id generation

UPD3: Added ActionName2

Upvotes: 6

Related Questions