Importing Data

Last Post 20 Jun 2007 08:27 AM by wnajm. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
wnajm
New Member
New Member

--
30 May 2007 02:21 PM
I am using a Database to collect information about a proprietary database that lacks proper documentation for the tables and their definitions and finding information in the proprietary (I know it sounds redundant but it is needed). The database i am creating contains a table called "TABLE" and another called "FIELD". They are linked by the TableID field.

The information for the fields is imported from the proprietary system. I am importing the data into a table in SQL Server called [FFDCSTBP]

I need a stored procedure to collect the following data from the [FFDCSTBP] table (FieldName, FieldDescription) and then insert those two pieces of information into the FIELD table in my database.
I also would like the procedure to get the highest ID number in the FieldID field from the FIELD table and increment it by one and assign that ID to the inserted record. And last I would like to specify manually which TableID is assigned to the whole operation (it will be the same ID for every record imported from [FFDCSTBP].

To that end I wrote the following stored procedure but it is not working. I think I had made an error somewhere but can't figure where.

CREATE PROCEDURE [dbo].[FieldInsert]
@FieldID int = 0,
@FieldName nvarchar(255),
@FDesc nvarchar(255),
@TableID int

AS
BEGIN
SET NOCOUNT ON;
DECLARE FieldInfoCursor CURSOR FOR
SELECT
[Name],
[Description]
FROM dbo.FFDCSTBP

OPEN FieldInfoCursor
FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO
dbo.FieldTest (
[FieldID],
[Name],
[Description],
[TableID])
VALUES (
@FieldID,
@FieldName,
@FDesc,
4)

SET @FieldID = @FieldID + 1
FETCH NEXT FROM FieldInfoCursor INTO @FieldName, @FDesc
END

CLOSE FieldInfoCursor
DEALLOCATE FieldInfoCursor
END

Thanks for any help you may be able to provide.
SQLUSA
New Member
New Member

--
31 May 2007 05:45 PM
Comments:

1. In RDBMS, a table consists of columns and rows.

2. To increment rows, use the IDENTITY(1,1) function.

3. About the incorrect population, can you descrie it?

Kalman Toth
SQL Server 2005 Training
wnajm
New Member
New Member

--
01 Jun 2007 10:52 AM
the population doesn't occur at all... that's the problem with it. The procedure is not working.

SQLUSA
New Member
New Member

--
01 Jun 2007 01:41 PM
When you run this query, what do you get?

SELECT
[Name],
[Description]
FROM dbo.FFDCSTBP


wnajm
New Member
New Member

--
01 Jun 2007 02:16 PM
I get a list of all the names and descriptions in the table dbo.FFDCSTBP

it is quite a long list
nosepicker
New Member
New Member

--
01 Jun 2007 05:22 PM
First of all, this method of populating the table using a cursor is highly inefficient. You'd be better off using a table with an identity column to get sequentially incremented id's. That being said, looks to me like the problem with your stored procedure is that your variables at the top are being used as parameters instead of local variables. Instead of this:

CREATE PROCEDURE [dbo].[FieldInsert]
@FieldID int = 0,
@FieldName nvarchar(255),
@FDesc nvarchar(255),
@TableID int

Do this:

CREATE PROCEDURE [dbo].[FieldInsert]
@FieldID int = 0,
@TableID int

AS

DECLARE
@FieldName nvarchar(255),
@FDesc nvarchar(255)

Then you can actually use these variables in your cursor (even though I don't recommend using a cursor).


wnajm
New Member
New Member

--
04 Jun 2007 02:55 PM
what do you recommend using instead??

and how is this different than my code?
nosepicker
New Member
New Member

--
04 Jun 2007 04:23 PM
For your FieldTest table, change the FieldID to an identity column. Then, that value will automatically increment every time you insert into that table. You can then insert the entire contents of the FFDCSTBP table into FieldTest in one statement, without using a cursor or loop:

INSERT INTO FieldTest ([name], [description], [TableID])
SELECT [name], [description], 4
FROM FFDCSTBP
wnajm
New Member
New Member

--
20 Jun 2007 08:27 AM
thanks a bunch!
You are not authorized to post a reply.

Acceptable Use Policy