How to add a new row to a sql server table in the first position (from python)

Question

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

score 3 · Accepted Answer · answered Aug 02 '21 at 13:18

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.

How to add a new row to a sql server table in the first position (from python)

1 Answers1