Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [insert]

A core statement in SQL that does what it says on the tin: inserts data into a table.

Filter by
Sorted by
Tagged with
99 votes
3 answers
477k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
Stéphane's user avatar
  • 5,565
33 votes
2 answers
65k views

Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO [DestinationLinkedServer].[...
Guillermo Gutiérrez's user avatar
36 votes
1 answer
104k views

Optimal way to ignore duplicate inserts? [duplicate]

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (...
Dave Jarvis's user avatar
10 votes
1 answer
70k views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
Virul Patel's user avatar
5 votes
1 answer
5k views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT NOT NULL, ...
Carey's user avatar
  • 151
5 votes
1 answer
11k views

Move values from one table to another one via stored procedure in sql server 2008

I have two tables Login (userid, username, password) audit_login (userid, Transaction_Date, Status (success or failed) Now I want create a stored procedure in which, if anyone wants to login then ...
user31595's user avatar
28 votes
1 answer
94k views

Benefits of using WITH TABLOCK on an INSERT

Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model....
Mark Freeman's user avatar
  • 2,181
7 votes
2 answers
6k views

What causes large INSERT to slow down and disk usage to explode?

I have a table of about 3.1 million rows with the following definition and indexes: CREATE TABLE digiroad_liikenne_elementti ( ogc_fid serial NOT NULL, wkb_geometry geometry(Geometry,4258), ...
jeran's user avatar
  • 73
4 votes
1 answer
383 views

At which point does SQL Server validate data for insertion

I have a query to insert data from one table into another. One of the source columns is larger than the destination table's, although the actual data selected in the query isn't. So e.g. the source ...
user1430949's user avatar
3 votes
4 answers
31k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
arthur's user avatar
  • 888
400 votes
4 answers
928k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
Jonas's user avatar
  • 33.2k
31 votes
2 answers
29k views

Does Postgres preserve insertion order of records?

For example when I'm using query which returns record ids INSERT INTO projects(name) VALUES (name1), (name2), (name3) returning id; Which produce output: 1 2 3 Will this ids point to corresponding ...
Sergey's user avatar
  • 413
5 votes
1 answer
1k views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
arthur's user avatar
  • 888
4 votes
2 answers
7k views

Mysql load from infile stuck waiting on hard drive

I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
Spencer Rathbun's user avatar
32 votes
2 answers
145k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
nojetlag's user avatar
  • 2,877
14 votes
3 answers
29k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
Rob Johansen's user avatar
10 votes
1 answer
94k views

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
user2493976's user avatar
5 votes
1 answer
9k views

How to set identity_insert to on in SQL Server

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
Joe Essey's user avatar
  • 351
3 votes
2 answers
8k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
quanta's user avatar
  • 1,006
33 votes
3 answers
80k views

Efficient INSERT INTO a Table With Clustered Index

I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER. E.G.: INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) SELECT TRACKING_NUMBER, COL_B, ...
GWR's user avatar
  • 2,807
26 votes
4 answers
38k views

How to limit maximum number of rows in a table to just 1

I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added....
Dib's user avatar
  • 447
24 votes
1 answer
18k views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
Yugo Amaryl's user avatar
11 votes
6 answers
35k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
Uday's user avatar
  • 814
10 votes
4 answers
18k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
inohiro's user avatar
  • 345
3 votes
1 answer
823 views

What does k=v do in the context of INSERT INTO tbl VALUES (expr)

Coming off this answer, I'm wondering why the lexer allowed it to get all the way down to the geometry grammar in this INSERT statement CREATE TABLE foo ( x varchar(255) ); > INSERT INTO foo ...
Evan Carroll's user avatar
  • 63.4k
3 votes
2 answers
3k views

INSERT rows in two tables preserving connection to a third table

I'm stuck with a query for which I can't seem to find and elegant solution without looping. Relevant bits of the schema: CREATE TABLE sites ( site_id SERIAL PRIMARY KEY, ... ); CREATE TABLE ...
Pierluca's user avatar
3 votes
1 answer
34k views

how to insert a list of varchar values, into a single column table?

In SQL Server 2005: CREATE TABLE __RADHE ( itemNo varchar(10) not null primary key clustered ); GO INSERT INTO __RADHE ??? '34926840', '34927020', '34927202', '34927384', '34927566', '34927830',...
Marcello Miorelli's user avatar
2 votes
1 answer
4k views

Deadlock on MySQL insert statments

Using MySQL v5.7.18 on Windows Server 2008 R2, we are having intermittent deadlock issues during insert queries and are looking for some guidance on how to mitigate them. Below are the inserts that ...
Doug Van De Motter's user avatar
1 vote
1 answer
118 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
user avatar
1 vote
1 answer
814 views

Oracle: Inserting into table error - either duplicate column name or invalid datatype

I'm trying to insert data from an external table. INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */ INTO construct (construct_id, n_term , enz_name, ...
ellie-lumen's user avatar
1 vote
1 answer
4k views

"Mysql has gone away" heavy multiple insert

I am stuck because Mysql server will go away after executing this part of code this function will update the database with the new RSS feeds which are stored in ToUpdate but Mysql stops working. Every ...
user539656's user avatar
31 votes
2 answers
31k views

Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each ...
Louis Somers's user avatar
18 votes
2 answers
4k views

Disk space full during insert, what happens?

Today I discovered the harddrive which stores my databases was full. This has happened before, usually the cause is quite evident. Usually there is a bad query, which causes huge spills to tempdb ...
HoneyBadger's user avatar
16 votes
4 answers
12k views

Progress of SELECT INTO statement

Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records. The statement looks something like SELECT ... INTO ...
Dan's user avatar
  • 397
12 votes
2 answers
4k views

How does IDENTITY_INSERT affect concurrency?

I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support. Under certain circumstances, it archives and incomplete post from the posting queue ...
Metaphor's user avatar
  • 906
11 votes
5 answers
56k views

Insert into select into multiple related tables using INSERT_IDENTITY

Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 ...
m4rc's user avatar
  • 235
7 votes
2 answers
4k views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...
Andrei Rînea's user avatar
7 votes
1 answer
3k views

Insert multiple rows into a table with only an IDENTITY column [duplicate]

I have a table called dbo.Groups defined like this: CREATE TABLE dbo.Groups ( GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY ); The table really consists of just the one IDENTITY column. ...
Andriy M's user avatar
  • 23k
7 votes
2 answers
2k views

Insert only deadlocks

We have an application inserting data into a table. Unfortunately, we are getting deadlocks and the deadlocks are coming from inserts only. We are seeing the inserts take key locks in a different ...
GoodwinSQL's user avatar
6 votes
1 answer
5k views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...
ahron's user avatar
  • 803
6 votes
2 answers
7k views

Is there a way to clone a row in a "maintainable" way (with PostgreSQL)?

I often have to clone rows in my applications so I do it the long way : INSERT INTO mytable (a, b, c) SELECT a,b,c FROM mytable WHERE id=12; (id is declared as a SERIAL PRIMARY KEY, 12 being the row ...
JC Boggio's user avatar
  • 197
6 votes
1 answer
2k views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
Robert Oschler's user avatar
6 votes
2 answers
9k views

MySQL auto increment column increases after insertion error occurs

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row ...
Leonardo Fachini Martins's user avatar
5 votes
1 answer
1k views

Insert randomly takes 10+ seconds to complete

I have an insert statement generated by the .NET Entity Framework. In most cases, this particular insert will execute in 0ms according SQL Server Profiler. One out of every 30 or so inserts will ...
Casey Wilkins's user avatar
5 votes
1 answer
1k views

INSERT duplicate rows for multiple, cascading 1:M child tables

Imagine multiple parent-child tables with 1:M relationships. I would like to "cascade"-insert duplicate rows based on root parent table row selection. Each table has IDENTITY primary key and ...
zam6ak's user avatar
  • 471
5 votes
2 answers
3k views

order of inserting record is mismatching

Please check the below table structure and records to be inserted in that table. CREATE TABLE [tabGeneraltable]( id int identity, [codGenLedger] [uniqueidentifier] NOT NULL, [codInvoice] [...
user1942247's user avatar
4 votes
1 answer
3k views

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date): | COLUMN_NAME | TYPE | |---------------|-------------|...
tacticz03's user avatar
4 votes
2 answers
2k views

What principles should I follow when entering data into a complex relational database?

I'm new to Databases, so forgive me if this is a basic question. I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex: I ...
Lou's user avatar
  • 181
4 votes
3 answers
4k views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
artfulrobot's user avatar
4 votes
3 answers
38k views

INSERT and DELETE in single operation (moving from one table to other)

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no ...
insaner's user avatar
  • 189