Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL CTE Insert Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 6:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 43, Visits: 191
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





Post #1438141
Posted Tuesday, April 2, 2013 8:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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




 
Post #1438150
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse