Import CSV into SQL Server

Asked

Viewed 216 times

-1

I tried to import a database using csv to a ready-made sql server base that we have deployed to other companies.

But in the system when registering, a client for example, uses MAX(ID)+1 to generate the Id for a given table. Now when I try to import these new data that are in csv, the id is null and because it is the primary key does not allow to import.

How can I perform this Max(id) to set the table id in the csv file import.

database sql-server csv

  • Change the field type to IDENTITY and delete the column from the CSV file

  • 2

    "MAX(ID)+1" is a bad technique for generating keys. Details in the article Generation of numerical sequences -> https://portosql.wordpress.com/2019/04/21/geracao-de-sequencias-numericas/

  • The import is performed in a unique mode, that is, only by accessing the table? What is the approximate number of CSV file records?

1 answer

1

You can import the CSV file to a temporary table and from this temporary table make the inclusion in the definitive table using something like

-- código #1
declare @Ultimo int;

BEGIN TRANSACTION;

SELECT top (1) @Ultimo= id
  from tabela with (tablockx)
  order by id desc;

INSERT into tabela with (tablockx) (id, demais colunas)
  SELECT @Ultimo + row_number() over (order by (SELECT 0)),
         demais colunas
    from #tabelatemp;

COMMIT;

It is a suggestion that you should evaluate whether the table lock is obtained or not.

You need to be aware of the growth of transaction log while the import is underway.

If you want the value generated for "id" to be in the order of a field in the CSV file, replace (SELECT 0) by the name of the respective column in the temporary table.

It is possible to import directly from the text file to the table when replacing #tabelatemp for OPENROWSET (BULK ...). Details in the article "Bulk import of data”.

Browser other questions tagged database sql-server csv

You are not signed in. Login or sign up in order to post.