|
|
Hi,
I need to import 1000s of users to the database. And there values need to be added to 6-7 different tables. Doing it manually would be a nightmare.
I think i can use Cursor for this sort of stuff but never dealt with it in past. Any suggestions?
Thank you .
|
|
|
|
|
You can first create a text file containing the user records, if you don't already have one.
Then use your database engine's import function to load the text file in to a table in one step. I can't give you the actual command to do this because I don't know what database engine you are using.
You said you need to load them into many different tables. You may need to modify the text file a bit to load them into these tables because these tables may have different structures.
If an import function is not available for the database engine you are using, you will need to write a small program to insert the records to the tables.
Hope that helps.
|
|
|
|
|
Sql server 2008.
i have already imported the excel spreadsheet with all the users info to database. Now for instance there are different table for address field, different table for name field , differnt table for email etc. So my question is do i have to use curser so that i can insert the users value from this temp table to all the necessary table. if yes then how or if not then what's the best way ?
Thank You.
Andyyy
Reply·
|
|
|
|
|
In this case all you need to do is to create these "other tables", and then use "INSERT...SELECT..." query to pull out the necessary fields from the temp table and insert the records to the new tables.
You can also create the tables and insert the records in one step by using the "SELECT...INTO..." statement.
Good luck!
|
|
|
|
|
What does 'user' mean here ? Are they database users or your application users (stored in something like Users table) ?
And what database do you use ?
|
|
|
|
|
Good point! The question did not describe that clearly.
|
|
|
|
|
Sql server 2008.
i have already imported the excel spreadsheet with all the users info to database. Now for instance there are different table for address field, different table for name field , differnt table for email etc. So my question is do i have to use curser so that i can insert the users value from this temp table to all the necessary table. if yes then how or if not then what's the best way ?
Thank You.
Andyyy
|
|
|
|
|
If your database is properly designed and normalized, then simple update statements will work, otherwise you may have to use cursors.
|
|
|
|
|
simple update statement works to enter one record by manually writing the details that i need to update but how to go to the next row in the table, get all the details and enter it to specific tables.
So i am not interested in this manual process. Looking for something automatic - say cursor ? but not getting how to use that.
|
|
|
|
|
The INSERT and UPDATE statements have a syntax that can be used to update multiple rows in the destination table from values in one or more source tables. The syntax is different for each database.
These are examples for SQL Server:
INSERT INTO EmployeeMaster (FirstName, LastName, EmpID)
SELECT EmpFirstName, EmpLastName, EmpID
FROM EmployeeTemp
WHERE (...........)
UPDATE EmployeeMaster
SET EmployeeMaster.FirstName = EmployeeTemp.EmpFirstName,
EmployeeMaster.LastName = EmployeeTemp.EmpLastName,
EmployeeMaster.EmpID = EmployeeTemp.EmpID
FROM EmployeeTemp T INNER JOIN EmployeeMaster M ON T.EmpID = M.EmpID
WHERE (...........)
|
|
|
|
|
|
Sql server 2008.
i have already imported the excel spreadsheet with all the users info to database. Now for instance there are different table for address field, different table for name field , differnt table for email etc. So my question is do i have to use curser so that i can insert the users value from this temp table to all the necessary table. if yes then how or if not then what's the best way ?
Thank You.
Andyyy
Reply·
|
|
|
|
|
Hai all,
I am searching one string (ex.Hello-Welcome) in text file using SQL query.
its searching Hello and Welcome seperatly and giving a result , i think bcoz hyphen(-) is a word breaker
in SQL .can any one give me the idea how to resolve the problem.
i need to use hyphen in searching string, and i need a combined string search result.
how to form the query?, if any one have sample pls send me.
thanks
mahe
|
|
|
|
|
Let me know if u r not looking for this -
declare @t table(record varchar(100))
insert into @t
select 'Hello-World' union all
select 'code - project codes are good' union all
select 'hello' union all
select ' world' union all
select ' how are u-?'
Query
select * from @t where record like '%[-]%'
Output:
record
Hello-World
code - project codes are good
how are u-?
Niladri Biswas
modified on Tuesday, December 1, 2009 8:12 AM
|
|
|
|
|
thanks 4 ur reply
its like same but i am searching the hyphenated string
in text file.
if u know any SQL query to searchin the string in any file pls let me know.
thanks
mahe
|
|
|
|
|
How are your searching?
Using Like , or some varieant of FullText searching?
|
|
|
|
|
i am searchin with CONTAINS keyword.
|
|
|
|
|
pls help me out to display a string for example 'abcd' 1000 times
using select statement in SQL
|
|
|
|
|
a)
select REPLICATE ('abcd', 1000)
b)
PRINT 'abcd'
GO 10000
c) If using sql server 2005+
with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)
select data from cte option (maxrecursion 0)
d)
DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)
Niladri Biswas
modified on Tuesday, December 1, 2009 6:10 AM
|
|
|
|
|
thanks for the response
but this gives me an output in this manner
abcdabcdabcdabcd..................
i m looking for
abcd in 1000 records
likes this
abcd
abcd
abcd
abcd
.
.
.
.
.
.
.
.
|
|
|
|
|
Follow the third approach i.e.
recursive cte
Niladri Biswas
|
|
|
|
|
thanks
its coming
how will i be able to insert that 'abcd' 1000 times in my insert given below
insert into [dbo].[Test](NAME)
|
|
|
|
|
A) With recursive cte approach
declare @t table(name varchar(50))
;with cte as
( select 1 as rn,'abcd' as data
union all
select rn+1, 'abcd' as data from cte where rn<1000)
insert into @t
select data from cte option (maxrecursion 0)
select * from @t
B) Xquery approach
declare @t table(name varchar(50))
DECLARE @x XML
SELECT @x = REPLICATE ('<x>abcd</x>', 1000)
insert into @t
SELECT i.value('.', 'VARCHAR(MAX)') ReplacedStrings
FROM @x.nodes('//x') x(i)
select * from @t
Niladri Biswas
|
|
|
|
|