0

I have an sql table Tab 1 in a Database. It is ordered in descent way based on the date. This table has to be updated with new records from python. In python I have another table Tab 2 similar to this one (same column names) but new data again ordered in descent way as well. I need to copy/insert the records in Tab 2 into Tab 1. In particulat only those records which are new (it happens Tab 1 has records already present in Tab 2 and those do not have to be copied/inserted again)

In details, once these 2 tables are available, the code opens sql server and looping through the row of the Tab 2, it checks if that record is a new one. If YES, there is INSERT query which is supposed to place the new record at the first position of Tab 1. So that the final version of Tab 1 is still ordered in descent way (the new records should be at the beginning of Tab 1). However, this is not happening, the new records are added in the middle of Tab 1 or in some other position.

I tried using also INSERT TOP(1) to force this behaviour but is not working.

It should be an easy task for an expert (which I am not) to insert a new row in the first position of an existing SQL table, but I am stuck. Below the code:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
df = pd.read_sql_query('SELECT * FROM dbo.Twitter',cnxn) #--> df is *Tab 1*, it is in sql server

for index, row in df5.iterrows(): #-->df5 is the python table, its records have to be inserted
    if str(row.id) not in df.id.values: #--> if they are new
        cursor.execute("INSERT INTO Twitter (id, Date_Time,Author,Tweet,Link, Source, Is_retwitted, Sentiment) values(?,?,?,?,?,?,?,?)", row.id, row.created_at, row.author, row.full_text, row.entities,row.source, row.is_retwitted, row.Sentiment) #-->QUERY
cnxn.commit()
cursor.close()

Many thanks in advance

5
  • First of all, which specific column or columns determine the descending order you are talking about? And are you positive that the rows you are adding are newer than any of the already existing rows?
    – Andriy M
    Aug 2, 2021 at 11:09
  • the columns that determine the descending order is Date_Time. Yes I am positive because that is regulated by the if statement which checks on a unique id number (row.id). That has been tested on very small table to verify it. Simply the problem is the order
    – Luigi87
    Aug 2, 2021 at 12:10
  • Then could you please show how you are selecting the rows that you are seeing the newer rows in the middle rather than at the top? Would be preferable if you edit your question, as that seems to me an important detail to the problem description.
    – Andriy M
    Aug 2, 2021 at 12:13
  • Sure I will edit. but sorry, I have not got exactly what you want me to show. A screenshot of the resulting erroneous table Tab 1 or something else?
    – Luigi87
    Aug 2, 2021 at 12:42
  • I'm assuming that in that part of your application where you are consuming your rows in the descending order, you are using a SELECT statement. So it would be helpful to see that SELECT statement.
    – Andriy M
    Aug 2, 2021 at 13:26

1 Answer 1

3

A table isn't ordered. There is no such thing as "first row", "next row" etc in a table.

You can have ORDER BY in a SELECT statement which defines the order of the result. Without ORDER BY, the DBMS is free to return the rows in any order it feels like doing, for the moment.

So, what you try to achieve is impossible, by itself.

You need some column and have the contents of that column determine what you define as "order". I.e., something you can use in your ORDER BY when you SELECT from that table.

9
  • thanks for your answer, is it possible then to modify my INSERT statement by adding the ORDER BY somewhere in the query? can you suggest me how to modify that query? thanks
    – Luigi87
    Aug 2, 2021 at 13:20
  • 4
    I think that you misunderstood my answer. There's no such thing as ORDER BY for an insert statement. A table isn't ordered. Add a column to the table and populate it with a value that you then can use in ORDER BY when you SELECT from that table. Aug 2, 2021 at 13:25
  • 1
    @Luigi87: To try and put the right emphasis for you, you can't specify an order when adding rows to a table. But you can specify an order when reading/selecting rows from a table.
    – Andriy M
    Aug 2, 2021 at 13:28
  • 2
    If they are not able to write queries how can they see the data? If they are either able to write simple SELECT queries (in which case they can be taught to add the specific ORDER BY somecolumn DESC; to see them in the wanted order) or you can edit the application they use to see that data so the application has that ORDER BY. Aug 2, 2021 at 14:16
  • 2
    To add further emphasis: "my table 1 (the one in sql server) has to be ordered". That is impossible. There are nu such things as ordered tables in an RDBMS (like SQL Server). Either chose some different type of DBMS or re-think your approach according to our recommendations. Sorry... Aug 2, 2021 at 14:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.