Loading data via text file with identity column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loading data via text file with identity column

Author

Topic

benbrambila
Starting Member

3 Posts

Posted - 2011-09-08 : 13:15:17

I'm trying to append data into an existing table via an external data source such as a text file. I've looked into using the bulk insert command which would work expect the first column in the table is a sequence number that requires incrementing. The database contains a stored procedure to increment. The below code would be used to load one row. I need to load many. Any ideas

DECLARE
@new_event_chklist_ind_seqno NUMERIC(8),

EXECUTE Get_Next_Seqno 'system',@new_event_chklist_ind_seqno OUTPUT

INSERT INTO [PCMS-SMH].dbo.system
(system_seqno, system_code, name)

VALUES
(@new_event_chklist_ind_seqno,'15-1 Crude','Crude')

Ben

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 15:40:47

We'll need to see the code of the stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

benbrambila
Starting Member

3 Posts

Posted - 2011-09-08 : 16:45:23

The stored procedure contains the following code.

USE [PCMS-SMH]
GO
/****** Object: StoredProcedure [dbo].[get_next_seqno] Script Date: 09/08/2011 12:40:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object: Stored Procedure dbo.get_next_seqno Script Date: 5/22/03 2:13:56 PM ******/
--
ALTER PROCEDURE [dbo].[get_next_seqno]
@tablename char(25),
@next_seqno numeric(8) OUTPUT
AS
SELECT @next_seqno = next_seqno
FROM sys_table_dict
WHERE table_name = @tablename

UPDATE sys_table_dict
SET next_seqno = @next_seqno + 1
WHERE table_name = @tablename

I'm playing with creating a temp table but again i need to populate it. The temp Table would contain three columns: Seqno; System_Code; System_Name. The Code and Name are of type Char(50) and are user defined while the Seqno is incremented based on the last created seqno.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 16:49:48

Do you actually require this manual incrementing? Why can't you use an identity column?

Your stored procedure could cause data collisions as you aren't locking the row. You must hold a lock until the transaction completes for a manual incrementor like this.

To answer your question, you'll need to block out a set of values in sys_table_dict. Load the data into a staging table, do a count(*), and then block out that many values. Then you can join to a tally table to get each value and insert using that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

benbrambila
Starting Member

3 Posts

Posted - 2011-09-09 : 09:32:10

Thank you. I'm trying to load data into a purchased software package, this is the reason i can not use an identity column. I'm begining to understand the data collisions issues with this type of programing. Thank you for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources