| Author |
 Topic  |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
 Posted - 17 March 2001 : 17:38:37
|
Ok here is my problem. I am trying to write a script that will take data from an old table and insert it into the new table.
The old table has the members ID attached to each of its messages. The column names in SQL have changed, and some of the customers have around 1,000 entries.
I have read somewhere about a SQL Statement called "Insert Into" or something like that. Im not sure how to use that or even if it is what I want, but this is kinda what I was thinking.....
<%
strSQL = "SELECT * FROM shared WHERE UserID='" & strUserID & "'"
set rs = my_Conn.Execute (strSQL)
if rs.eof or rs.bof then
Response.Write ("there was an error")
else
do until rs.eof
strSql = "INSERT INTO new_shared (UserID,Name,Email) VALUES ('" & strUserID & "','" & rs("Username") & "','" & rs("EmailAddress") & "')"
my_Conn.Execute (strSql)
rs.MoveNext
loop
end if
%>
Would something like this work? Or could it cause problems during transfer of large amounts of data? Any better way of doing this?
Brad |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 17 March 2001 : 19:23:47
|
but is it the best way? or just a way to do it?
what is the "Insert Into" SQL Statement? and would it be used for something like this?
Brad |
 |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 17 March 2001 : 19:28:33
|
Insert Into is the sql way of saying 'put these in here, under those columns'
That is the only way I can think of to do it...
----------------
Da_Stimulator
Need a Mod?
My Snitz Test Center
|
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 17 March 2001 : 19:49:46
|
how about a sample using "Insert Into" SQL command? 
Brad |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 March 2001 : 19:50:22
|
Check out SQL Server books online or msdn.microsoft.com/library, all the transact SQL documentation is available.
Select Into creates a new table and copies the specified fields into it in one statement.
======
Doug G
====== |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 17 March 2001 : 19:51:33
|
thanks doug ill go look...
doug, do you think the first example i made is good enough or should i look into another way?
Brad |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 17 March 2001 : 19:52:57
|
There is an example in your original post :)
here ya go...
INSERT INTO TableName (Column1, Column2) Values ('Value1', 'Value2')
----------------
Da_Stimulator
Need a Mod?
My Snitz Test Center
|
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 17 March 2001 : 19:55:01
|
oh im sorry im thinking of something totally different. i ready somewhere about a sql statement that will copy data from one table to another and you can specify what colum to copy it from and what column to insert it back into
any ideas what im thinking about
Brad |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 17 March 2001 : 20:11:16
|
lol oh yea i just cant think......
so you got an example of insert into?
Brad |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 18 March 2001 : 10:40:58
|
errr sorry i keep getting everything mixed up.
id like an example of "select into" where you select one table and the columns and tell it which columns and table to insert it into
Brad |
|
|
|
cevans
Junior Member
Canada
101 Posts |
Posted - 19 March 2001 : 09:28:17
|
Here your go:
select into new_tablename
col1, col2, ..., coln
from old_tablename
[where colx = xxx]
Note that the where clause is optional. If you want all columns in the old table, you can do this:
select into new_tablename
*
from old_tablename
The documentation for this would be found in the T-SQL reference in Books Online, as part of the documentation for SELECT. The INTO bit is an optional clause in the select statement.
However, there are drawbacks to doing select into.
First, you must have the bulk copy option set for the database (right click on the database in Enterprise Manager, choose Properties, and then the Options tab. There will be an option for select into/bulk copy.
Second, if you have any text fields in your table you should NOT use select into. If you do, you will no longer be able to backup just the transaction log, until you have performed a full database backup. Mind you, if you have truncate log on checkpoint set or are not doing transaction log backups, this isn't really a concern.
Third, things like default values, primary and foreign keys, and indexes will not be copied. You will need to go into the new table and recreate these manually afterward.
A much better option than SELECT INTO is to first create the table using a CREATE TABLE statement, and then insert into it like this:
insert into new_tablename (new_col1, new_col2, ..., new_coln)
select old_col1, old_col2, ..., old_coln
from old_tablename
As with any insert statement, if you're inserting data into all of the columns in the table, you can omit the (new_col1, ..., new_coln) clause as long as the select statement contains the correct number of columns (and they are in the correct order).
Clark |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 19 March 2001 : 18:13:36
|
I think the syntax should be SELECT col1, col2 INTO newtablename FROM table WHERE blah blah
======
Doug G
====== |
|
|
|
cevans
Junior Member
Canada
101 Posts |
|
| |
Topic |
|
Topic Locked
