February 9, 2009 at 2:51 am
hi everyone, i have the following table
DECLARE @users TABLE
(
userid int,
username VARCHAR(20)
)
INSERT INTO @users(userid,username)
SELECT 1,'Joe'
INSERT INTO @users(userid,username)
SELECT 2,'Smith'
INSERT INTO @users(userid,username)
SELECT 3,'John'
INSERT INTO @users(userid,username)
SELECT 4,'Simon'
SELECT * FROM @users
i want to copy each userid from the above table (@users) into my @state table...
DECLARE @state TABLE
(
stateid INT PRIMARY KEY,
userid INT,
userstate INT,
date DATETIME
)
SELECT * FROM @state
but also insert values for userstate and date for each userid
anyone have any advice for me.
thanks
February 9, 2009 at 3:07 am
Please could you be more clear on what you wish to do? I cant make any sense out of it.
February 9, 2009 at 3:14 am
i need to copy all the userid's from the @users table and insert them into the @state table with some other values for example....
insert into @state (stateid,userid,userstate,date)
select 1,1,5,'2009-02-09 12:13:50.887'
need a script to do this for each userid i have in the @users table...
February 9, 2009 at 3:25 am
OK......now from where are you getting these values for Stat and the others (except for the user id.....i know where that from thanks to the explaination).
If your requirement is to generate the insert statements,then the values for the others also has to obtained from somewhere..As of now, the only values that are available are that of the user ids...anyways I leave that for you to figure out..
I would just you could go for a While Loop or a cursor to take each record from the table and do the script generation ......
February 9, 2009 at 3:28 am
the other values i'll add on my own
can you give me an example of a while loop or cursor?
will appreciate it
February 9, 2009 at 3:47 am
ok an example with a cursor...
declare @userid int
declare insertcur cursor fast_forward
for
select userid from users
open inertcur
fetch next from inertcur into @userid
while @@FETCH_STATUS = 0
begin
--- put in watever logic is required
print 'insert into stats values(1,'+@userid+'5,getdate(),3)'
fetch next from inertcur into @userid
end
close inertcur
deallocate inertcur
now for the while loop, I ll leave that for you find out.....
February 9, 2009 at 7:12 am
How about a set based approach?
INSERT@state( stateid, userid, userstate, date )
SELECTm.stateid, u.userid, m.userstate, m.date
FROM@users u
INNER JOIN SomeUserAndStateMapping m ON u.userid = m.userid
--Ramesh
February 9, 2009 at 9:01 am
Hi
Agree with Ramesh solution. It is very simple. Never use curosors in SQL Server. They are 30% slower than normal queries.
Thanks -- Vijaya Kadiyala
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply