Here is the binary equivalent of COPY FROM for Python 3: from io import BytesIO from struct import pack import psycopg2 # Two rows of data; “id” is not in the upstream data source # Columns: node, ts, val1, val2 data = [(23253, 342, -15.336734, 2494627.949375), (23256, 348, 43.23524, 2494827.949375)] conn = psycopg2.connect(“dbname=mydb user=postgres”) curs … Read more
I don’t really have experience with TVP yet, however there is an nice performance comparison chart vs. BULK INSERT in MSDN here. They say that BULK INSERT has higher startup cost, but is faster thereafter. In a remote client scenario they draw the line at around 1000 rows (for “simple” server logic). Judging from their … Read more
Alternatively, if you are wanting to disable all triggers, not just those on the USER table, you can use: SET session_replication_role = replica; This disables triggers for the current session. To re-enable for the same session: SET session_replication_role = DEFAULT; Source: http://koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporarily/
There’s nothing wrong with your stored procedure code – the point is: the BULK INSERT command cannot accept a file name as a variable. This does work: BULK INSERT ZIPCodes FROM ‘e:\5-digit Commercial.csv’ WITH but this never works – within a stored proc or not: DECLARE @filename VARCHAR(255) SET @filename=”e:\5-digit Commercial.csv” BULK INSERT ZIPCodes FROM … Read more
mysqlimport. takes the same connection parameters as the mysql command line shell. Make sure to use the -L flag to use a file on the local file system, otherwise it will (strangely) assume the file is on the server. There is also an analogous variant to the load data infile command, i.e., load data local … Read more
you can save the xlsx file as a tab-delimited text file and do BULK INSERT TableName FROM ‘C:\SomeDirectory\my table.txt’ WITH ( FIELDTERMINATOR = ‘\t’, ROWTERMINATOR = ‘\n’ ) GO
If you are writing this in C# you might want to look at the SqlBulkCopy class. Lets you efficiently bulk load a SQL Server table with data from another source.
The insert trigger is called once for bulk inserts, but on the trigger you can use the special inserted table to get all the inserted rows. So, imagine you have an insert trigger like this one, that logs all the rows inserted into table create trigger trgInsertTable on dbo.table for insert as insert tableLog(name) select … Read more
You can disable and enable the indexes. Note that disabling them can have unwanted side-effects (such as having duplicate primary keys or unique indices etc.) which will only be found when re-enabling the indexes. –Disable Index ALTER INDEX [IXYourIndex] ON YourTable DISABLE GO –Enable Index ALTER INDEX [IXYourIndex] ON YourTable REBUILD GO
That’s run on the server, so its looking for C:\data.txt on the server’s C: drive. Also ensure the logon your using has read permissions on C:.