SELECT . . . INTO in SQL

A

Agus Budianto

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)
 
A

Agus Budianto

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: Finding Data That Is In One Table But Not The Other 4
Delete duplicates QUERY 0
Union Query by choice.. 11
Nesting outer joins with ODBC and Access 2003 3
INSERT / UPDATE Query in same operation 2
Query/Report Error 4
fields in a select query 1

Top