Select Into Statement I think, Basic question

gmoney060 · December 24th, 2004, 08:06 PM

I need to take recordsets from one table and transfer some of them to another table in a diffrent database. I know it is possible and I think i use the select into statement... I saw this on another post:

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

So would it be Select * From Players INTO where something

But how do i actually execute that.... Like how do i open the databases and such?

jemacc · December 25th, 2004, 08:18 PM

You can use it like this by creating a store procedure then executing it

Code:

create store procedure cr_sp_selectinto
as
select * into <destination> col1, col2, col3 
from <source>
where <criteria>

When referencing another database use four part naming

Code:

database.dbo.table.object

or

Code:

database..table

Code:

execute cr_sp_selectinto

Jaime E. Maccou

Anantsharma · December 27th, 2004, 07:14 AM

HI,

Yes its possible but be carefull while using this. This may cause loss of existing data.

Better u use this:-
-----------------

INSERT INTO DB1.DBO.TABLE1 (col1,col2) VALUES (SELECT COL1,col2 FROM DB2.DBO.TABLE2......)

THIS IS SAFE AND MUCH LOGICAL. For table names in different databases, use 4 part database name.

B. Anant

abhishek_255 · December 29th, 2004, 08:26 AM

IF the two databases on different servers then use this code:

INSERT INTO OPENDATASOUCE('SQLOLEDB', 'Data Source=Datasoucename;User ID=userid;Password=password')DB1.DBO.TABLENAME SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=Datasoucename;User ID=userid;Password=password' ).DB2.DBO.TABLENAME

Note : The First Datasouce contains the connection string where you want to append records.
The Second Datasource contains the connection string from where you want to fetch records.
IF They are on same server then use this code:

INSERT INTO DB1.DBO.TABLENAME SELECT * FROM DB2.DBO.TABLENAME

May be this help you

Regards,

Abhishek Kumar
Software Developer

mingssn · December 31st, 2004, 02:52 AM

this is to remind that after use SELECT INTO, check your index, constraint and default value. new talble might lost them.