Import table data into stored proc

Posted on 2009-04-27
Last Modified: 2012-05-06
I have a table that contains some contact data I need to import into my database.

Basically I need to know how to loop through the table and send each record to a stored proc that adds users like below (I know my syntax is way off).  I don't know if I should use a while loop or what, but I need to have the table values for parameters in the stored proc.

SELECT FirstName, LastName, etc FROM dbo.Import
INTO usp_AddContact FirstName, LastName, etc
Question by:sklarbodds
    LVL 21

    Expert Comment

    You can do something like this:


    DECLARE @Counter INT, 
    		  @FirstName VARCHAR(50), 
    		  @LastName VARCHAR(50), 
    SET @Counter = 1
    SELECT ROW_NUMBER() OVER (ORDER BY FirstName, LastName, etc) AS Row, FirstName, LastName, etc 
    INTO #Import
    FROM dbo.Import
    WHILE @Counter <= (SELECT MAX(Row) FROM #Import)
    	SELECT @FirstName = FirstName, @LastName = LastName, etc
    	FROM #Import
    	WHERE Row = @Counter
    	EXEC usp_AddContact @FirstName, @LastName, etc
    	SET @Counter = @Counter + 1

    Open in new window

    LVL 12

    Accepted Solution

    You can also use a cursor as below:

    	Declare @FirstName varchar(50);
    	Declare @LastName varchar(50);
    	DECLARE Contact_cursor CURSOR FOR
    		select FirstName, LastName from <yourtableName>
    	Open Contact_cursor
    	FETCH NEXT FROM Contact_cursor into @FirstName, @LastName
    			Exec usp_AddContact @FirstName, @LastName
    			FETCH NEXT FROM Contact_cursor into @FirstName, @LastName
    	CLOSE Contact_cursor
    	DEALLOCATE Contact_cursor

    Open in new window


    Author Comment

    Thanks to both of you, I ended up using the cursor because it seemed the most straightforward.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now