2

I have a problem where I am in the process of creating a database of teachers for my project. As a part of this process, I might have to use multiple "Insert" statements to enter all the various details about each teacher to create rows of data about them. So, if I have say a 1000 teachers, then I have to manually write a 1000 insert statements to create individual records for each of them. This is definitely a tedious exercise.

I was wondering if there might be a way or method by which I could minimize the time taken for this process!! Is there a stored procedure or something else which could possibly automate this task? Can I create and use something like Macros to the needful?

For example, a piece of MySQL code (individual statement) could be:

insert into <tablename> (attribute1, attribute 2, attribute 3, ....., attribute n) values (value1, value2, value3, ......., value n)

Any suggestions on this would be greatly appreciated. I would have to know the code in T-SQL (for MS SQL Server)

Thank you, Seth

Suraj Shrestha
  • 1,790
  • 1
  • 25
  • 51
Seth
  • 23
  • 3
  • 1
    maybe try a programming language that has loop structures and generates the SQL for you and stores it into a file that you can then just run? – Taryn East Feb 04 '15 at 03:41
  • What is the source for inserting data i mean from where you are getting `value1, value2, value3,....... value` – Pரதீப் Feb 04 '15 at 04:49
  • may this question help you... http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part – Dhruv Kapatel Feb 04 '15 at 05:09
  • Like "insert into Teachers ( School_ID, FirstName, LastName, PhoneNo) values (1, 'John', 'Smith', '555-5578'); and so on....." – Seth Feb 04 '15 at 05:19

1 Answers1

0

Download a first name database. For example from http://www.namepedia.org/en/firstname/

Then import this database into table, with identity insert.

Then you can only write script like that:

    DECLARE @i INT = 1000, @y INT = 1, 


WHILE @i != 0  
BEGIN



INSERT INTO yourtable (FirstName, LastName) 
SELECT FirstName, FirstName AS LastName 
FROM DownloadedTable a 
JOIN DownloadedTable b ON b.IdentityId = @i 
WHERE a.IdentityId = @y

    SET @i = @i-1

END

Or thisone better:

SELECT TOP 100 FirstName 
FROM DownloadedTable a
JOIN (SELECT TOP 10 FirstName as LastName FROM DownloadedTable ORDER BY FirstName Desc) b ON 1=1

If You Need real Last Names, search in phone databases... for

Matej Hlavaj
  • 900
  • 7
  • 10