Search code examples
mysqlsql-servert-sqlstored-proceduresmultiple-insert

Converting multiple SQL Insert statements into one single Insert statement

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

Solution

  • 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