Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Autoincrement field -> start with 1

Posted on 2012-08-24
12
Medium Priority
?
617 Views
Last Modified: 2012-08-27
Hi!

Have a stored procedure that read a csv-file using bulk insert.

One of the fields in the table have to start with 1, and then autoincrement
2,3,4,5,6,7.......

Next time i run the stored procedure, it must start with 1 again.

How can i do this ?
0
Comment
Question by:team2005
  • 8
  • 4
12 Comments
 
LVL 2

Author Comment

by:team2005
ID: 38328884
Hi!

No one knows how to solve this ?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38329517
You should always import your data into a staging table.  You can then validate it prior to populating your production tables.

In your specific case, here is what you should do:
1. Create a staging table that has an IDENTITY column.
2. In you routine that imports the data have the following steps:
TRUNCATE TABLE YourStagingTable
Import data into YourStagingTable
Validate the data
INSERT the data in YourStagingTable into your production table.

This way the column in your production table will always restart at 1 for every time you import a new file.
0
 
LVL 2

Author Comment

by:team2005
ID: 38335691
Hi!

I try to use this code, but cant get this to work ?

INSERT INTO dbo.HAU_SalesLine (LINENUM,ITEMID, 
      SALESPRICE,SALESUNIT,QTYORDERED,LINEAMOUNT,CUSTACCOUNT,CUSTOMERREF,SALESID,CREATEDDATETIME,DATAAREAID)

      SELECT dbo.VEC_Fakturalinjer.Løpenummer,dbo.VEC_Fakturalinjer.Vectura_materialnummer, 
      dbo.VEC_Fakturalinjer.Enhetspris,
      dbo.VEC_Fakturalinjer.Enhet,dbo.VEC_Fakturalinjer.Fakturert_kvantum,
      dbo.VEC_Fakturalinjer.Fakturert_volum,
      '50015',
      dbo.VEC_Fakturalinjer.Vectura_bilagsnummer,
      (Select Ordrenummer_nesteledige from VEC_Parameters),CURRENT_TIMESTAMP,
      'VVV' 

      FROM dbo.VEC_Fakturalinjer
      WHERE dbo.VEC_Fakturalinjer.ArtikkelAX=1 AND dbo.VEC_Fakturalinjer.kodefirma=87

      SET @i = 1
      SET @NumRec=(SELECT COUNT(*) FROM dbo.VEC_Fakturalinjer WHERE dbo.VEC_Fakturalinjer.kodefirma=87)
      WHILE EXISTS (SELECT * FROM dbo.HAU_SalesLine WHERE SALESID = (Select Ordrenummer_nesteledige from VEC_Parameters))
      BEGIN

        update dbo.HAU_SalesLine set LINENUM=@i
        

        -- increment counter for next employee
        SET @i = @i + 1
      END

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:team2005
ID: 38336010
Hi!


I tryed to print @i
and it shows
1
2
3
4
5
..

But how to set the current record ?

WHILE EXISTS (SELECT * FROM dbo.HAU_SalesLine WHERE SALESID = (Select Ordrenummer_nesteledige from VEC_Parameters))
      BEGIN

        update dbo.HAU_SalesLine set LINENUM=@i
        

        -- increment counter for next employee
        SET @i = @i + 1
      END

Open in new window

0
 
LVL 2

Author Comment

by:team2005
ID: 38336196
Hi!

Almost get this right now

I have made a field in table dbo. HAU_SalesLine called test
This field is a IDENTITY(1,1) field

When i insert into my table dbo. HAU_SalesLine the field
test is filled up :
1
2
3
4
5
6
..

That is working fine, but if i want to start at 1 again...

I want the first 100 records in table dbo. HAU_SalesLine
to set the field test to 1,2,3,4,5,,,,,,100

And the next 100 records, must start from 1 again

How ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38336246
How ?
Did you see my comment?
0
 
LVL 2

Author Comment

by:team2005
ID: 38336308
Hi!

Yes i see your comment acperkins...

The strange thing is this:

Import data into table
dbo. HAU_SalesLine

If the import, import 100 lines
the IDENTITY field is set correct to (1,2,3,,,,,100)

But next time i run the import (stored procedure)
it no start at 1 again ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38336425
Then you need to re-read my comment as you are clearly not following.  Just to be clear:  There are two tables:
1. Staging table that is used to import the data. It has an IDENTITY column that is reset every time.
2. Production table that is used to copy the data from the Staging table.
0
 
LVL 2

Author Comment

by:team2005
ID: 38336949
Hi!

I found this i can use in my stored procedure.
(DBCC CHECKIDENT('dbo.VEC_Fakturalinjer', RESEED, 1))
But if i run my stored procedure 4 times
i wil have 4 of 2
                4 of 3
                4 of 4
                .....

But only 1 of 1... ?
Bugs ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38337332
I give up.

Good luck.
0
 
LVL 2

Author Comment

by:team2005
ID: 38337355
Hi!

It working now, but dident work earlyer today ?

Give you the points acperkins
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38337358
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 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