Sql/Coldfusion - 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

Sql/Coldfusion

Author

Topic

boab88
Starting Member

2 Posts

Posted - 2009-04-24 : 10:32:07

Hey This might be a coldfusion or it might be a sql question.

I want to insert values into a table so I've got an sql statment like so:

INSERT INTO UserRating (rating, playerid, fixtureid ,UserID)
<cfoutput>VALUES (#therating#,#theid#,7,2000)</cfoutput>

This works fine and inserts the values into the correct table, The problem is that #therating# and #theid# are lists of multiple values: so for example theid=564723891 and therating=1141141131

Currently when I run the query the table ends up like this:

Userid playerid fixtureid rating
2000 564723891 7 1141141131

I want it to loop through the values so that:
Userid playerid fixtureid rating
2000 5 7 1
2000 6 7 1
2000 4 7 4
etc.

I think the problem is because they are lists rather than when i normally take data out of a statment. Any ideas how to change it?

Thanks Mike

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-25 : 03:03:33

Hi Mike,
Me too a CF developer.glad to see u here

In mssql, u can achieve multiple insertion in the following way, will be the easiest method

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3

So using cfquery tag, u pls try something like below.
<cfquery ur attributes>
INSERT INTO MyTable (FirstCol, SecondCol)
<cfloop>
use list loop here to produce selects
</cfloop>
</cfquery>

Try pls and let me know
Thanx...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources