The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 18, 2019, 07:25:34 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: bulk insert select question  (Read 2004 times)
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« on: July 23, 2009, 10:45:41 AM »

YO!

The Kaptain is doing one of these shots

INSERT INTO tablename ( name, stuff, sequence)
SELECT name, stuff, Huh??
FROM some table
where stuff = 'yer momma'

now, what we're wondering here is how we can generate a number and increment it for each row.  We need to store a sequence number with each row so we can query in a specific way for them.  Any ideas?

Logged

We can't stop here, this is bat country.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: July 23, 2009, 11:01:49 AM »

er. autoincrement?
Your destination table just needs an autoincrement row.
Or do you need the source also to have the number?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #2 on: July 23, 2009, 11:07:36 AM »

nah, not auto increment

i need each recordset to start with 0

so if i insert 100 rows it starts with 0 then the next 100 starts with 0

the table has an auto increment as the PK already Smiley


i really should have phrased it better in the first post.  i wonder how many people just rolled their eyes and called me a fucktard when they read that.
Logged

We can't stop here, this is bat country.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #3 on: July 23, 2009, 12:14:09 PM »

Code:
SET @cnt := ( SELECT MAX( sequenceNumber )
FROM myTable
WHERE myID =2 ) ;

SELECT @cnt := @cnt +1, myData
FROM myTable


Bingo.


now to figure out how to make it work with my bulk insert and i win the game.
« Last Edit: July 23, 2009, 12:19:43 PM by KaptainKrayola » Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: July 24, 2009, 12:40:55 PM »

did you know that if you combine another field with your autoincrement PK it will restart with each change in the first field?

For example: say you had a field "thedate" that was just the 10 digits of today: 2009-07-22 ... then you had an autoincrement field right next to it and both of those fields are part of the PK. Then every time the date field changed, the autoinc will start at 1 again.

Is there a way to use something like that?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!