Tag Info

New answers tagged

0

At 250/hour, all possible techniques are plenty fast. Anyway, I will throw my 2 cents in... Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs. Multi-row INSERT may lead to gaps in AUTO_INCREMENTs. In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use. (REPLACE them all dups ...


1

Right off the bat, if you're targeting a column insert ( insert table ( col_1, col_2 ) ), you need to specify an insert value for both columns. From the posted syntax, it appears you're targeting 3 columns ( user_id, group_id, default_group ) but only inserting 1 explicit value ( user_group.user_id ). To start, I recommend commenting out the insert portion ...


2

Your INSERT statement is malformed; you may want to try something like: Insert into usergroup (user_id, group_id, default_group) Select ug.user_id, 1234 AS group_id, ug.default_group from [user_group] ug join [group] g on ug.group_id = g.id where g.name = 'someName'; Replace 1234 in the query above with the id of the group you wish to add for each user ...


1

You can use a hierarchical query to generate a sequence of dates, something like this: insert into my_table (my_date_column) select dt + (level - 1) from (select date '<your start date>' dt from dual) connect by dt + (level - 1) <= date '<your end date plus 1 day>'; Date arithmetics: dt + (level - 1) -- are smart enough to know when one ...


0

Take a look here. CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ); and also at this: CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );


1

Consider using a table-valued parameter to pass many rows of data as a single proc call, or alternatively, bulk copy directly into the table. These techniques will improve insert throughput by orders of magnitude compared to singleton inserts, even if those are multi-threaded. An incremental clustered index will provide the best insert performance against ...


0

If this is going to be a Write Once, Read Many database, consider your NoSQL options like Riak. The key indexing the values can be anything you want and the record can be accessed quickly. If you're addressing a big data issue, where you want to bulk insert your records and then perform analysis on them, I suggest you go with a Hadoop cluster, upload your ...


3

You just need to specify the first SiteInfo node using [1] update [dbo].[Sites] SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') where Siteid = 1


1

To me, it looks like you're on the right track, if I'm understanding your question clearly (which I'm not sure that I am. :P ) To me, it looks like you simply need a DECLARE block, where you are declaring variable values which will persist throughout the function block. Add DECLARE CREATE OR REPLACE FUNCTION insaft_function() RETURNS TRIGGER AS DECLARE ...


3

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to your previous question: SELECT in trigger function in two tables However, you are not passing integer numbers, but the text representation of integer ...


2

You could do something like: INSERT INTO test_table (first_name,last_name,full_name) SELECT first_name,last_name , CONCAT(coalesce(first_name,''),coalesce(last_name,'')) FROM ( SELECT 'raju' as FIRST_NAME ,'pydi' as LAST_NAME ) AS T; I still think it's a pretty bad idea to store full_name though. You could create a view like: ...


2

You can specific the columns firstname and last_name in your INSERT statements. As the MySQL INSERT documentation says: An expression expr can refer to any column that was set earlier in a value list. For example, you can do this because the value for col2 refers to col1, which has previously been assigned: INSERT INTO tbl_name (col1,col2) ...


1

I've modified your TRIGGER and I used ON DUPLICATE KEY UPDATE to UPDATE all the fields when your NEW.lastmodifieddate be greater than targets.lastSyncAt. Some errors: You've used a function on a field label in pdone.LCAPITAL(first), that can't be possible. You've used the same function now trying to CONCAT 2 field labels, instead table fields name ...


3

Instead of issuing those checks as separate SELECT statements, make them part of the INSERT statement. You will need to replace the INSERT...VALUES syntax with the INSERT...SELECT one to be able to use a WHERE clause: INSERT INTO waypoint_checkins (runner_id, waypoint_id, time) SELECT runners.id, %waypoint_id, %time FROM runners WHERE name = ...


1

You already found that you have to use dynamic SQL with EXECUTE. What you are missing: If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways. You need to defend against SQL injection. Table names have to be ...


1

Try this form: EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW; It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays. An even more modern and cleaner version (quote the table's name if necessary): EXECUTE format('insert into %I values ($1.*)', child) USING NEW;



Top 50 recent answers are included