inserting multiple record per each record read

Question

inserting multiple record per each record read

nick-226936

SSC Journeyman

Points: 99
More actions
June 9, 2008 at 2:30 am

#122588

I need to add multiple records to table 'b' for each record that I read from table 'a'
table a:
col1 col2
widget_a 3
widget_b 8
widget_c 7
table b: should look like this
col1 col2
widget_a 3
widget_a 3
widget_b 8
widget_b 8
widget_c 7
widget_c 7
I used teh following:
DECLARE
@v_column1 varchar(50),
@v_column2 varchar(50),
@v_column3 varchar(100),
@message varchar(2000)
DECLARE temp_info CURSOR FOR
SELECT state,part,[description]
FROM dbo.table_a
OPEN temp_info
FETCH NEXT FROM temp_info INTO
@v_column1,
@v_column2,
@v_column3
WHILE @@FETCH_STATUS = 0
BEGIN
set @message = 'INSERT INTO dbo.table_b (state, part, [description])
VALUES (' +
char(39) + @v_column1 +char(39) + ' ,'+
char(39) + @v_column2 + char(39) + ' ,'+
char(39) + @v_column3 +char(39) + ')'
PRINT @message
FETCH NEXT FROM temp_info INTO
@v_column1,
@v_column2,
@v_column3
END
CLOSE temp_info
DEALLOCATE temp_info
this only reads and creates an insert statement for each record read.
can anyone help, thanks nv

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

Wrap your dynamic SQL in a WHILE loop with MaxCount as its limit. This solution is good if you don't have too many records to deal with. If you have a lot, a set-based solution may be better.

DECLARE @MaxCount tinyint, @Count tinyint

SET @MaxCount = 2

-- cursor creation

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Count = 1

WHILE @Count <= @MaxCount

BEGIN

set @message = 'INSERT INTO dbo.table_b (state, part, [description])

VALUES (' +

char(39) + @v_column1 +char(39) + ' ,'+

char(39) + @v_column2 + char(39) + ' ,'+

char(39) + @v_column3 +char(39) + ')'

PRINT @message

SET @Count = @Count + 1

END

FETCH NEXT FROM temp_info INTO

@v_column1,

@v_column2,

@v_column3

END

-- close/deallocate

nick-226936 SSC Journeyman Points: 99 More actions · Answer 2

thanks!, that worked. I just couldn't get mine to work 🙂

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

No need for a cursor or While loop here... makes the code too long and slow. Try this instead...

--===== This makes a little test table according to the post

CREATE TABLE dbo.TableA (Col1 VARCHAR(15), Col2 INT)

INSERT INTO dbo.TableA

(Col1, Col2)

SELECT 'widget_a',3 UNION ALL

SELECT 'widget_b',8 UNION ALL

SELECT 'widget_c',7

--===== This makes rows based on the data in Col2

-- Just add this to an insert.

SELECT a.*

FROM dbo.TableA a

CROSS JOIN dbo.Tally t

WHERE t.N <= a.Col2

--===== Or, if you'd rather, this makes just 2 row for each row

-- Just add this to an insert.

SELECT a.*

FROM dbo.TableA a

CROSS JOIN dbo.Tally t

WHERE t.N <= 2

In case you don't know what a Tally table is, check out the following article, please...

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)