Inserting values from 1 table to another using looping.

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

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting values from 1 table to another using looping.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-11 : 09:23:07

Harpal Singh writes "Hi All,

I hav 2 tables 1 CompleteMatrix(CM) comtaining CLI,D1,D2,D3....,D31 fields and 2nd MatrixDetail(MD) containing Cli,cnt,Dt.

There is Value in Cli of CM and inserting values in matching D1,D2.. respectively according to date in dt field of MD. after match CLI of CM and MD.

In code below Line 10 gives the result what i want but when im trying to execute that cvery Query im getting an error.

-----Code-----

declare @i integer
declare @dt datetime
set @i=1

while (@i<=3)
begin

print 'Date is: ' + cast(@dt as varchar)
Line 10: --print 'insert into CompleteMatrix (D'+ rtrim(ltrim(cast(@i as char)))+') select cnt from matrixdetail,CompleteMatrix where datediff(d,dt,'+ rtrim(ltrim(cast(@dt as char))) +')=0 and completematrix.cli=matrixdetail.cli'
Line 11: insert into CompleteMatrix (D'+ @i +') select cnt from matrixdetail,CompleteMatrix where datediff(d,dt,'+ rtrim(ltrim(cast(@dt as char)))+')=0 and completematrix.cli=matrixdetail.cli

set @i=@i+1

end

--sp_help CompleteMatrix

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '+ @i +'."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-11 : 10:23:18

-- The error is because of the way u use the variable
-- U cannot use the variable like this
-- I couldn't find what this variable does in the query
-- U may be trying to Concatenate @i value to D and create D1, but in that u have to have Dynamic SQL
-- If u want all the fields to be written, u can get it done by lots of easy means
-- Give Some Sample Data of ur 2 tbls (make sure to put it in code tags) and the data u would c after inserting

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources