INSERT multiple rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT multiple rows

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-10-14 : 12:43:57

I have a stored procedure which takes in a list of IDs. I want to insert the rows for those IDs into another table.

Here's my schema:

---------------------------------------------
tblEntries
---------------------------------------------
EntryID
---------------------------------------------

---------------------------------------------
tblRoundEntries
---------------------------------------------
RoundEntryID | RoundID | EntryID
---------------------------------------------

I'm trying to get rows from tblEntries and put them in tblRoundEntries. Here's my stored procedure:

@EntryIDs nvarchar(800),
@RoundID int

AS

DECLARE @sqlstr varchar(8000)

SET @sqlstr =
'INSERT INTO tblRoundEntries
(RoundID, EntryID)
SELECT ' + @RoundID + ', EntryID
FROM tblEntries
WHERE EntryID IN (' + @EntryIDs + ')'

EXEC(@sqlstr)

The error I'm getting says:

Conversion failed when converting the varchar value 'INSERT INTO tblRoundEntries
(RoundID, EntryID) SELECT ' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 12:47:26

why use dynamic sql at all? use below

@EntryIDs nvarchar(800),
@RoundID int

AS


INSERT INTO tblRoundEntries
(RoundID, EntryID)
SELECT  @RoundID, EntryID
FROM tblEntries
WHERE ','+@EntryIDs +',' LIKE '%,' + CAST(EntryID  AS varchar(10)) + ',%'
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 02:52:50

Print @sqlstr

and see if it is valid statement

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources