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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply