How to implement bulk insert feature in Sql Server 2005

Question

I have a table in database called user which has following entries.

Userid FirstName LastName
1          fa      la
2          fb      lb
3          fc      lc

and another table userform

Userformid Userid  Form   Code modifieddate  Isclosed   IsForm 
1             1     ff     cc     somedate     0           0
2             1     gg     rr     somedate     0           0
3             1     bb     bb     somedate     0           0
4             2     ss     aa     somedate     0           0
5             2     qq     sa     somedate     0           0
6             3     ws     xc     somedate     0           0

Now i have to insert new record for every userid in userform table and only Form and Code column should get copied in inserted row from userform table only by latest modifieddate ( something like:- order by modifieddate desc.)

Output should be in userform table :
    Userformid Userid  Form   Code modifieddate  Isclosed   IsForm 
    1             1     ff     cc     somedate     0           0
    2             1     gg     rr     somedate     0           0
    3             1     bb     bb     somedate     0           0
    4             2     ss     aa     somedate     0           0
    5             2     qq     sa     somedate     0           0
    6             3     ws     xc     somedate     0           0
newly added row
    7             1     bb     bb     newdate     0           0
    8             2     qq     sa     newdate     0           0
    9             3     ws     xc     newdate     0           0

There are 6000 record in user table and userform table.

How can we achieve this using bulk insert feature OR using any other technics in sql server 2005.I don't want to use CURSOR.

AdaTheDev · Accepted Answer · 2010-12-21 10:24:49Z

1

Something like this, making use of ROW_NUMBER should do the trick. Try it first without the INSERT to check the results it returns. If that is then what you want, just uncomment the INSERT line and go ahead.

DECLARE @NewDate DATETIME
SET @NewDate = GETDATE()

--INSERT UserForm (UserId, Form, Code, modifieddate, IsClosed, IsForm)
SELECT UserId, Form, Code, @NewDate, IsClosed, IsForm
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY modifieddate DESC) AS RowNo, 
        UserId, Form, Code, IsClosed, IsForm
    FROM UserForm 
) x
WHERE RowNo = 1

answered Dec 21, 2010 at 10:24

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sukhjeevan Over a year ago

Hi AdaTheDev! Thanks for the tips.Its really worked for me.I got some record where RowNo = 2.Can u explain me what does that mean?.

AdaTheDev Over a year ago

@Sukhi - glad I could help. What the ROW_NUMBER with the PARTITION clause is doing, is generating an incrementing row number for each record in the resultset partitioned by user id - that means the row number resets to 1 for each distinct user id so if you have 3 records for a given user id, they will be assigned row numbers 1, 2 and 3 (in order of modified date descending).

Collectives™ on Stack Overflow

How to implement bulk insert feature in Sql Server 2005

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related