SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL CTE Insert


TSQL CTE Insert

Author
Message
nfs_john
nfs_john
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 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
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 1721
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






 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search