Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jeff06
Posting Yak Master
166 Posts |
 Posted - 2007-11-07 : 14:20:07
|
| I do not have right to load a table with 600,000 records into a database. the table currently stored in a cvs file.I want to hard code a temp table in the DB using sql query analyzer.I already generate 600,000 lines of txt which looked like:insert into #basicinfo values ('9098292063','5 to 9','Less Than $500000',null,'5812','3 to 5 years','Pacific')insert into #basicinfo values ('9199670909','20 to 49','Less Than $500000',null,'5812','3 to 5 years','Eastern')insert into #basicinfo values ('3362920226','50 to 99','Less Than $500000',null,'5812','3 to 5 years','Eastern')insert into #basicinfo values ('8142341228','50 to 99','Less Than $500000',null,'5812','3 to 5 years','Eastern')insert into #basicinfo values ('4809646567','1 to 4','Less Than $500000',null,'7538','More than 10 years','Mountain')insert into #basicinfo values ('6034443338','1 to 4','Less Than $500000',null,'5812','2','Eastern')..........and stored thoes text in a txt file code.txt.The trouble now is that I can not load code.txt into sql query analyzer.how can i create a big temparary table with sql query analyzer?ThanksJeff |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-07 : 14:23:32
|
| Create Table #BasicInfo ( colname1 char(1) not null, colname2 char(8) not null, --etc)Insert Into #BasicInfo( colname1,colname2, etc)Select '9098292063','5 to 9','Less Than $500000',null,'5812','3 to 5 years','Pacific' UNION ALLSelect '9199670909','20 to 49','Less Than $500000',null,'5812','3 to 5 years','Eastern' UNION ALLetc etc |
 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2007-11-07 : 14:30:00
|
| By "hard code" do you mean create and leave in place? Why not just create a table using CREATE TABLE? Why the need to make it temp?Nathan Skerl |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-07 : 14:40:02
|
Temp tables are automatically destroyed when last connection is ended...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-11-07 : 14:45:13
|
| I see thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 00:37:24
|
| "Temp tables are automatically destroyed when last connection is ended..."##Temp tables aren't ... |
|
|
|
byang
Starting Member
7 Posts |
Posted - 2007-11-08 : 11:49:08
|
| try bulk insert, which will allow you load text file into a table. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-08 : 13:50:14
|
quote:
Originally posted by Kristen
##Temp tables aren't ...
Yes they are.The global temp table is destroyed when the connection that the global temp table was created in, is terminated and all other processes leaves the table.Maybe better phrased in Books Onlinequote:
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
That's why I wrote "last connection" before.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 14:11:46
|
| Interesting because I've had to manually drop them when something died half way through and I needed to re-run the process manually. I guess something was keeping that session alive (maybe connection pooling even?)Kristen |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-08 : 18:08:02
|
quote:
Originally posted by Kristen
Interesting because I've had to manually drop them when something died half way through and I needed to re-run the process manually. I guess something was keeping that session alive (maybe connection pooling even?)Kristen
Same here, if it doesn't "finish", the temp table already exists and I have had to do the same thing--F5 a drop #temp table statement or disconnect QA and reconnect. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-09 : 01:41:44
|
| If you are in a QA "Window" that will keep the connection open |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-09 : 01:42:07
|
| ... which is often a benefit for things like this! |
|
|
|
|
|
|
|
|
How to creat large temp table?