SELECT . . . INTO in SQL

G

Guest

Dear All,

I'm new to SQL.
I want to have a passthorugh query that will copy Table1 to Table2.

"SELECT * INTO Table2 FROM Table1"

Table1 is in the current mdb file. Table2 would be in DB1 database in the
SQL Server.

What is the sintax to refer to the tables?

Any reply is highly appreciated.

Rgds,
Agus Budianto
 
D

Douglas J. Steele

You can specify the connection information in an IN clause:

SELECT * INTO Table2 IN "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
FROM Table1

or, DSN-less

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;UID=sa;PWD=;"
FROM Table1

For trusted connection, that second would be

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;Trusted_Connection=Yes"
FROM Table1

but you're actually better off creating a linked table to Table2 (it's more
efficient)
 
G

Guest

Thanks Douglas,

I am not linking the table to avoid user edit the data directly into the
table.
Is it the right way of preventing it?

Rgds,
Agus Budianto
 
D

Douglas J. Steele

It's a way.

You could also implement Access security, so that they can't use tables
directly, but that's a lot of work.

Another option would be to create a linked table before you run your INSERT
INTO query, and then delete it once you're done.
 
A

adsl

Agus Budianto said:
Dear All,

I'm new to SQL.
I want to have a passthorugh query that will copy Table1 to Table2.

"SELECT * INTO Table2 FROM Table1"

Table1 is in the current mdb file. Table2 would be in DB1 database in the
SQL Server.

What is the sintax to refer to the tables?

Any reply is highly appreciated.

Rgds,
Agus Budianto
Click to expand...
 
A

adsl

Douglas J. Steele said:
You can specify the connection information in an IN clause:

SELECT * INTO Table2 IN "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
FROM Table1

or, DSN-less

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;UID=sa;PWD=;"
FROM Table1

For trusted connection, that second would be

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;Trusted_Connection=Yes"
FROM Table1

but you're actually better off creating a linked table to Table2 (it's
more efficient)
Click to expand...
 
A

adsl

Agus Budianto said:
Thanks Douglas,

I am not linking the table to avoid user edit the data directly into the
table.
Is it the right way of preventing it?

Rgds,
Agus Budianto
Click to expand...
 
A

adsl

Douglas J. Steele said:
It's a way.

You could also implement Access security, so that they can't use tables
directly, but that's a lot of work.

Another option would be to create a linked table before you run your
INSERT INTO query, and then delete it once you're done.
Click to expand...
 
A

adsl

Douglas J. Steele said:
It's a way.

You could also implement Access security, so that they can't use tables
directly, but that's a lot of work.

Another option would be to create a linked table before you run your
INSERT INTO query, and then delete it once you're done.
Click to expand...
 
A

adsl

Agus Budianto said:
Thanks Douglas,

I am not linking the table to avoid user edit the data directly into the
table.
Is it the right way of preventing it?

Rgds,
Agus Budianto
Click to expand...
 
A

adsl

Agus Budianto said:
Thanks Douglas,

I am not linking the table to avoid user edit the data directly into the
table.
Is it the right way of preventing it?

Rgds,
Agus Budianto
Click to expand...
 
A

adsl

Douglas J. Steele said:
You can specify the connection information in an IN clause:

SELECT * INTO Table2 IN "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
FROM Table1

or, DSN-less

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;UID=sa;PWD=;"
FROM Table1

For trusted connection, that second would be

SELECT * INTO Table2 IN "ODBC;Driver={SQL
Server};SERVER=MyServerName;DATABASE=pubs;Trusted_Connection=Yes"
FROM Table1

but you're actually better off creating a linked table to Table2 (it's
more efficient)
Click to expand...
 
A

adsl

Agus Budianto said:
Dear All,

I'm new to SQL.
I want to have a passthorugh query that will copy Table1 to Table2.

"SELECT * INTO Table2 FROM Table1"

Table1 is in the current mdb file. Table2 would be in DB1 database in the
SQL Server.

What is the sintax to refer to the tables?

Any reply is highly appreciated.

Rgds,
Agus Budianto
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I quickly change the table name in a query? 15
sql query and WHERE condition 3
How to limit insertion of records 3
Replace text with related number 1
Query multiple table relationships subquery? 1
Query from expression 2
JOIN issue: Valid Syntax to refer to (In FROM clause) the Selected Fields from Query 15
Need pointers on joins in queries. 6

Top