April 2, 2013 at 6:35 pm
Hello,
I was sure I had asked this before but I can't find that post so my apologies for the repeat question. I need to do an insert into two tables X times. X is TBD from a control table. Let's just say three times. So I need the same record inserted 3 times except for one field that will be whatever X is. Table A & B have a relationship. For example:
INSERT TableA (ColumnA, ColumnB, ColumnX)
VALUES (ValueA, ValueB, NULL)
INSERT TableB (ColumnA, ColumnB, ColumnC)
VALUES (SCOPE_IDENTITY, ValueB, ValueC)
Now I need to do this three time for our example and I don't want RBAR. I believe you do this with a join or a cte I think. Not sure what to brush up on exactly.
So this would get me my loop values
DECLARE @Loop TABLE (RepeatCount INT)
INSERT @Loop SELECT NumRepeats FROM ControlTable WHERE ID = 123
So I would then use a CTE to join the inserts with the @Loop correct?
JB
April 2, 2013 at 8:12 pm
First set up some sample data (it would help in the future if you provide a populated temp table for sample data):
/* THIS IS JUST FOR GENERATING SAMPLE SOURCE DATA */
IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
DROP TABLE #TestData
;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,0.15,1,9),
(1,0.05,2,8),
(2,0.05,1,7),
(3,0.05,1,7),
(3,0.10,2,9),
(3,0.25,3,6)
) DATA (ValueA,ValueB,ValueC,ValueX))
SELECT
IDENTITY(INT,1,1) AS ID
,ValueA
,ValueB
,ValueC
,ValueX
INTO
#TestData
FROM
sampledata
SELECT * FROM #TestData
/* END SOURCE DATA */
Now to do what you want in a single statement without using dynamic SQL I had to let ValueC ride the coattails of the first insert so I needed to add ValueC to TableA. You need to get the value into TableB somehow and this was the easiest method I could think of. The other option would just be two individual insert statements.
IF OBJECT_ID('tempdb..#TestTableAlpha') IS NOT NULL
DROP TABLE #TestTableAlpha
IF OBJECT_ID('tempdb..#TestTableBravo') IS NOT NULL
DROP TABLE #TestTableBravo
CREATE TABLE #TestTableAlpha (
[ID] INT IDENTITY(100,1) NOT NULL, --100 so we can see the result easier
[ColA] INT NULL,
[ColB] DECIMAL(5,2) NULL,
[ColC] INT NULL,
[ColX] INT NULL,
PRIMARY KEY (ID))
CREATE TABLE #TestTableBravo (
[ID] INT IDENTITY(1,1) NOT NULL,
[ColA] INT NULL,
[ColB] DECIMAL(5,2) NULL,
[ColC] INT NULL,
PRIMARY KEY (ID))
INSERT INTO #TestTableAlpha
(ColA,ColB,ColC,ColX)
OUTPUT
INSERTED.ID AS ColA,
INSERTED.ColB,
INSERTED.ColC
INTO #TestTableBravo
SELECT
ValueA,
ValueB,
ValueC,
ValueX
FROM #TestData
SELECT * FROM #TestTableAlpha
SELECT * FROM #TestTableBravo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply