LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » SELECT * INTO Question

SELECT * INTO Question

Options

Previous Topic · Next Topic

wdismukes

Posted: Monday, February 09, 2009 11:32:27 AM

Rank: PFY

Joined: 5/3/2007
Posts: 24
Points: -22
Where do you live?: Houston, Texas

I have the following table structure:

CREATE TABLE dbo.TestTable
(ID INT IDENTITY (1,1) NOT NULL
,Col1 VARCHAR (10) NOT NULL);

If I run the following:

SELECT * INTO TestDB.dbo.TestTableCopy
FROM TestDB.dbo.TestTable;

The structure of dbo.TestTableCopy is identical to dbo.TestTable.
Both tables have an IDENTITY column. This also works if I go from one database
to another on the same server.

However, if I run

SELECT * INTO dbo.TestTableCopy
FROM [LinkedServer].LinkedServerDBName.dbo.TestTable;

The table dbo.TestTableCopy does not have an IDENTITY column.

The [LinkedServer] is a hosted database website.

Any ideas why this might be happening?

bklr

Posted: Tuesday, February 10, 2009 12:03:49 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

u are accessing new remote server so that u will not get the identity column in 2nd condition
when using with into u will not get the constraints used in the original table

if u want the identity in new table
then
select identity(int,1,1) as id , * INTO TestDB.dbo.TestTableCopy
FROM TestDB.dbo.TestTable;

try like this u will get the identity column in ur target table

wdismukes

Posted: Tuesday, February 10, 2009 8:22:59 AM

Rank: PFY

Joined: 5/3/2007
Posts: 24
Points: -22
Where do you live?: Houston, Texas

Bklr - Thanks for the reply. Can you point me to the documentation of this behavior when accessing a new remote server. I haven't been able to find it.

Using the IDENTITY function to create a new IDENTITY column will not work for me in this situation. I need to maintain the incrementing behavior and current values of the original ID column. I am trying to avoid using IDENTITY_INSERT and a column list.

Any ideas?

bklr

Posted: Wednesday, February 11, 2009 1:05:14 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

up to my knowledge
u have to take insert scripts for table with identity_insert on
and execute in required server with ur target table
i think so.............

Rajesh Jonnalagadda

Posted: Tuesday, April 14, 2009 8:51:58 AM

Rank: Newbie

Joined: 1/27/2009
Posts: 2
Points: 6
Where do you live?: India

Try this

SET IDENTITY_INSERT [ database_name] . [ schema_name ] .[ table name] ON

SELECT * INTO dbo.TestTableCopy
FROM [LinkedServer].LinkedServerDBName.dbo.TestTable

SET IDENTITY_INSERT [ database_name] . [ schema_name ] .[ table name] OFF

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.