Handlind data - 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

Handlind data

Author

Topic

atmonline
Starting Member

14 Posts

Posted - 2008-07-02 : 19:57:05

I am doing data migration on some of the tables.

i am using bulk insert.

i am using some data for example 100 rows to table 1. then using the autogenerated value from table1 as foreign key to add

data to table2.

my problem is i have assumed that i will be only the person adding data to system. but our system is live and there could be

some one users adding data. so when i add data to table2 i want to make sure that i am inserting only those records that are

associated with my insert in table1

i have used the logic

------------------

id = get the max(records) from table 1

insert data in to table 1

insert data in to table2 from table2 where table1.primarykey>id

so here it is very clear that i will get records inserted from live users

sample code
----------

--add records to js_APPLICATIONS table for each row in sheet3
--Find the Total no: of records in JS_Applications

DECLARE @total_applicants_before_insert INT
SELECT @total_applicants_before_insert = coalesce(max(ApplicationID),0)FROM JS_Applications

INSERT INTO JS_Applications (
JS_UserID,
ApplicationTitle,
DateCreated,
Completed
)
SELECT
UserID uid,
'Manually Received Application' mp,
ReqDate + '08:0' ReqDate,
1 into #application
FROM
(
SELECT UserID, sheet3.ReqDate
FROM sheet3
LEFT JOIN #sheet1 ON #sheet1.appkey=sheet3.appkey
)
--add records to js_applications is now complete

--applicationid in the js_applications table need for other tables.
-- the appkey from sheet1 is not useful here because applicationid is autogenerated so won't match.

select * from #application

INSERT INTO JS_PersonalProfile
(
ApplicationID,
DateCreated,
FirstName,
AlternatePhone
)
SELECT
ApplicationId,
js_Applications.DateCreated,
AlternatePhone
FROM js_Applications
LEFT JOIN Js_MasterProfile ON Js_MasterProfile.js_userid=js_Applications.js_userid

where ApplicationId > @total_applicants_before_insert

i thought i will add all reocrds to temp table but how will do that

if add records to origial table then only i get the autogenerated id's .how do i handle this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:18:37

Dont you have an audit column in your table like created_by or modified_by to designate who created/modified the records?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources