• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Import table data into stored proc

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
0
sklarbodds
Asked:
sklarbodds
1 Solution
 
JestersGrindCommented:
You can do something like this:

Greg



DECLARE @Counter INT, 
		  @FirstName VARCHAR(50), 
		  @LastName VARCHAR(50), 
		  etc
 
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)
 
BEGIN
	
	SELECT @FirstName = FirstName, @LastName = LastName, etc
	FROM #Import
	WHERE Row = @Counter
	
	EXEC usp_AddContact @FirstName, @LastName, etc
	
	SET @Counter = @Counter + 1
	
END

Open in new window

0
 
expertsoulCommented:
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
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			Exec usp_AddContact @FirstName, @LastName
		
			FETCH NEXT FROM Contact_cursor into @FirstName, @LastName
		END
	CLOSE Contact_cursor
	DEALLOCATE Contact_cursor

Open in new window

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now