Copying records from one table to another in different databases 3

[Steve101]

I am looking for the simplest way in SQL to copy the contents of one table, to an identifcally structured table in another database (on the same server). The tables have an Identify field.

In Query Analyser, I select the destination database, then when I use the syntax:

INSERT INTO tblCompany
SELECT *
FROM PDAdmin.dbo.tblCompany

I get the diagnostic:

Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'tblCompany' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I understand all of this; is there a simple way of getting round this without having to explicitly include all fields in the SQL; also, do I have any control of how the indentity field values are transferred (eg. I might want to duplicate them, or alternatively, re-initialise them).

TIA,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[vidru]

1) To copy the contents without resetting the original identity column, qualify all of the column names except for your identity field:

  INSERT INTO tblCompany (Field2, Field3, Field4)
  SELECT Field2, Field3, Field4
  FROM PDAdmin.dbo.tblCompany

2) To keep the original identity value, temporarily allow an identity insert. You'll still need to qualify the field names:

  SET IDENTITY_INSERT tblCompany ON
  INSERT INTO tblCompany (CompanyID, Field2, Field3, Field4)
  SELECT * FROM PDAdmin.dbo.tblCompany
  SET IDENTITY_INSERT tblCompany OFF

-dave

 

[Steve101]

Thanks Dave. I was kind of hoping that there was a way to do this without having to explicitly enter all of the (non identify) column names (as there are lots of columns). Any other options here (eg. BCP, DTS)?

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Dave,

I've developed some code to do what I want; however, I need to know how I can incorporate the IDENTITY_INSERT capability in this, to allow me to control the indentity key in the same way:

The code is:
[tt]
Function LoadSQLTable(LoadFromTableName, LoadToTableName)
'---------------------------------------------------
'Transfer a table's data from one server to another;
'---------------------------------------------------
DoCmd.Hourglass True

Dim DB As Database: Set DB = CurrentDb
Dim RSI As DAO.recordset
Dim RSO As DAO.recordset

TruncateTable "DSNIntegratecPD", LoadToTableName

sq = "SELECT * " & _
"FROM " & LoadFromTableName
Set RSI = DB.OpenRecordset(sq, dbOpenDynaset, _
dbSeeChanges)
sq = "SELECT * " & _
"FROM " & LoadToTableName
Set RSO = DB.OpenRecordset(sq, dbOpenDynaset, _
dbSeeChanges)
While Not RSI.EOF
RSO.AddNew
For I = 0 To RSI.Fields.Count - 1
''If I <> 0 Then
RSO(RSI.Fields(I).Name) = RSI(RSI.Fields(I).Name)
''End If
Next I
RSO.Update
RSI.MoveNext
Wend
RSI.Close: RSO.Close
DB.Close

DoCmd.Hourglass False
End Function
[/tt]

The first (zero'th) column is always the identity column. Works great when this column is included in the loop (ie. to clone the identity values).

Do you have any idea how I can exclude this id column in order to get fresh identity values; ie. when I remove the comments to prevent the zero column assignment, I get an Update error which I'm sure is associated with the SET IDENTITY_INSERT not being set to ON for the recordset.

Any help would be greatly appreciated,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[vidru]

Steve,

Maybe you could create a new recordset that has all of the columns except the identity column???

I'll have to give it some more thought. Good luck, and I'll let you know if I come up with something else when I've got some time.

-dave

 

[mrdenny]

You could do a select from the information_schema view, and copy and paste that into the query window. That will save you from typing at least.

Denny

--Anything is possible. All it takes is a little research. (Me)