Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

insert query from one database to another database on same server

i am using VB6.0 and SQL Server 2008 R2,
i am trying to insert a Record from one database to another database but same table name and same structure..

From VB6 to ACCESS i was using following query.
NPcon.Execute ("INSERT INTO B_Dtl select * from [;DATABASE=" & App.Path & "\Trans.mdb;PWD=******].B_Dtl where BNo = " & Text1.Text & "")
0
crystal_Tech
Asked:
crystal_Tech
2 Solutions
 
AnujCommented:
INSERT INTO <DestinationDB>.<Schema>.<Dest.Table>
SELECT Columns
FROM <SourceDB>.<SourceSChema>.<SourceTable>

eg.

INSERT INTO DestDB.dbo.MyTable
SELECT *
FROM SourceDB.dbo.MyTable
0
 
Jesus RodriguezIT ManagerCommented:
Also you have to check if has an identity(autoincrement) on the selected fields that will be insert on the table that you want.
0
 
crystal_TechAuthor Commented:
i am getting an error:

An explicit value for the identity column in table "MyDb.dbo.MyTable" can only be specified when a column list is used and IDENTITY_INSERT is ON
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jesus RodriguezIT ManagerCommented:
SELECT THE FIELDS WITHOUT IDENTITY MANUALLY OR SET

SET IDENTITY DATABASE.DBO.YOURTABLETOINSERT ON
0
 
Jesus RodriguezIT ManagerCommented:
FOR EXAMPLE IF YOU HAVE BOTH TABLES PRODUCTS  WITH THIS STRUCTURE

ID                INT IDENTITY(1,1)
PRODUCT   NVARCHAR(50)
UM              NVARCHAR(50)
PRICECOST  FLOAT

AND YOU DO THISD
INSERT INTO DB1.DBO.PRODUCTS
SELECT * FROM DB2.DBO.PRODUCTS

WILL GIVE YOU AND ERROR IF DONT HAVE SET IDENTITY_INSERT DB1.DBO.PRODUCT ON

YOU CAN DO
INSERT INTO DB1.DBO.PRODUCTS(PRODUCT,UM,PRICECOST)
SELECT PRODUCT,UM,PRICECOST
FROM DB2.DBO.PRODUCTS

AND NOT USE IDENTITY INSERT AND LET THAT THE IDENTITY ON THE SECOND TABLE WILL BE CREATE
0
 
crystal_TechAuthor Commented:
please explain me more..

Through front end (vb6) i am inserting record from source db to destination db and deleting from source db.
but some time i need to get back same record from destination db to source db.
0
 
crystal_TechAuthor Commented:
ok i understand
but there is more than 50 fields..
0
 
Anthony PerkinsCommented:
please explain me more..
It is quite simple:  Always include the column names in the INSERT statement as in:
INSERT (Col1, Col2, Col3, ...)
SELECT ...

It does not get more complicated than that.
0
 
Anthony PerkinsCommented:
but there is more than 50 fields..
If you find that is a lot of typing for you, than consider the following trick:
1. In SSMS go to Object Explorer
2. Expand databases.
3. Expand the database.
4. Expand Tables
5. Expand the table.
6. Drag the Columns collection to the query window.
7. Drop it and it will create a comma delimited string of all the columns.
8. (Optional) Remove the IDENTITY column if it is not required (You are not using SET IDENTITY_INSERT tablename ON)
0
 
crystal_TechAuthor Commented:
LOL :-)

Thanks .. let me type all the fields..
0
 
Jesus RodriguezIT ManagerCommented:
DON'T TYPE

GET THIS

DECLARE @COLNAME NVARCHAR(40)
DECLARE @LISTCOLUMNS NVARCHAR(MAX)

SET @LISTCOLUMNS=''
DECLARE TBL CURSOR FOR
                  SELECT name
                  FROM sys.columns WHERE object_id = OBJECT_ID('YOURTABLENAME')
OPEN TBL
FETCH NEXT FROM TBL INTO @COLNAME
WHILE @@FETCH_STATUS=0
 BEGIN
  SET @LISTCOLUMNS=@LISTCOLUMNS + @COLNAME +','
 FETCH NEXT FROM TBL INTO @COLNAME
END      
     
SET @LISTCOLUMNS=LEFT(@LISTCOLUMNS,LEN(@LISTCOLUMNS)-1)
PRINT @LISTCOLUMNS
0
 
crystal_TechAuthor Commented:
Getting an error:

Con.Execute "INSERT INTO NewDB.dbo.BDtl([Field1],[Field2],[Field3],[Field4],[Field4],[Field5],...etc]) " & _
"SELECT [Field1],[Field2],[Field3],[Field4],[Field4],[Field5],...etc] from OldDB.dbo.BDtl " & _
"where BNo = " & MBNo & ""



Can not insert the value NULL into column 'IdentityColumn', table 'myDB.dbo.Bilty_Detail'; columns does not allow nulls. Insert fails.
0
 
Jesus RodriguezIT ManagerCommented:
Can you post the structure of the tables please ?? Looks like is more than one Identity on this tables
0
 
crystal_TechAuthor Commented:
Sorry sir due to company policy i cant post structure.. :-(
0
 
Anthony PerkinsCommented:
GET THIS
Way too much work.  it is much easier just to drag the object from Object Explorer.

Can not insert the value NULL into column 'IdentityColumn', table 'myDB.dbo.Bilty_Detail'; columns does not allow nulls. Insert fails.
Perhaps you missed my comment:
Remove the IDENTITY column if it is not required (You are not using SET IDENTITY_INSERT tablename ON)
0
 
Jesus RodriguezIT ManagerCommented:
Hold On. How can be possible that if both tables has the same structure, the primary table have the null value on one Identity Field??
0
 
Jesus RodriguezIT ManagerCommented:
Did you're using this second table(where you insert the fields) as a temporary table ??? Can you drop this table and re-created the table on the fly?? Cna you modify the structure of the second table and remove the identity property on the fields that are identity?
0
 
crystal_TechAuthor Commented:
i am deleting record form table_A after inserting into Table_B

in case of mistake i need to get this record back from Table_B to Table_A

so inserting this record into Table_A and Deleting from Table_B

this is how managing all the records.
0
 
crystal_TechAuthor Commented:
i didn't remove identity field yet...
i can modify all tables and structure
0
 
Anthony PerkinsCommented:
i didn't remove identity field yet...
i can modify all tables and structure

I did not mean to remove it from the table, I meant for you to remove it from the INSERT statement in both the INSERT list as well as the SELECT list.
0
 
crystal_TechAuthor Commented:
i did that..
i didnt add identity field into insert statement.
0
 
Jesus RodriguezIT ManagerCommented:
Check this steps again please

- Check that both tables have the EQUAL structure (identities, allow null on fields..etc)
- Before set the query set the identity_insert to on on both tables
- run the query as show

  set identity_insert db1.dbo.table on
  set identity_insert db2.dbo.table on

  insert into db2.dbo.table
  select * from db1.dbo.table
  where db1.dbo.fieldontable='yourcondition'

there is no reason for the query that through an error if both tables have the same structure
0
 
crystal_TechAuthor Commented:
i checked both table .. and i found..something different in identitycolumn
one table has identity specification = yes and (is identity) = yes
another table has specification = no and (is identity) = no and identity increment also emtpy..

Please suggest me what should i do..
0
 
crystal_TechAuthor Commented:
set identity_insert SorcDB.dbo.MyTable on
  set identity_insert MyDb.dbo.MyTable on

Msg 8106, Level 16, State 1, Line 2
Table 'MyDb.dbo.MyTable' does not have the identity property. Cannot perform SET operation.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hi, since you cannot post the table structure, this is going to be difficult for us to answer :) So I'll try... As you compared the 2 tables and found a difference, please post the column that shows the difference, not much harm in there, right? The table MyDb.dbo.MyTable has the problem, the column that is not having the identity property will miss 'IDENTITY(1,1)' as shown in the example below:

Col1 INT NOT NULL IDENTITY(1,1)

Modify the MyDb.dbo.MyTable table, add the IDENTITY property.
0
 
Mark WillsTopic AdvisorCommented:
Let's say DB2 has the source table (S_Table) and DB1 has the destination table (D_Table).

Lets say that the Id column is named ID and the other columns are named col1 through to coln

Let's say if D_Table has the identity column (ID) then the insert will be something like :

INSERT DB1.dbo.D_Table (col1,col2,col3,,,,,,,coln)
SELECT col1,col2,col3,,,,,,,,coln
FROM DB2.dbo.S_Table S

Now, if ID is not an identity in D_Table but is a primary key, then you will need a value and should be able to simply pick it up from the S_Table

INSERT DB1.dbo.D_Table (ID, col1,col2,col3,,,,,,,coln)
SELECT ID, col1,col2,col3,,,,,,,,coln
FROM DB2.dbo.S_Table S
WHERE S.ID is not NULL

But if it ID in D_Table is supposed to be an identity then you should make it one and allow the system to assign the numbers - or - do you need to retain the original value ?
0
 
tliottaCommented:
due to company policy i cant post structure..

Why would posting a table structure violate policy? It would make sense not to post data contained within a table, but the structure is hardly meaningful to anyone here -- especially since a bit of obfuscation would change names.

That's not to say that policy doesn't forbid it. It's just asking about why, if any reason is known.

Tom
0
 
Mark WillsTopic AdvisorCommented:
*laughing* company policies can be a bit like that sometimes. Blanket application of security without consideration of what it impacts. Such is corporate life sometimes...

I have no problems with not seeing structures, and dont think we should pressure to see the structures as long as a potential game of "twenty questions" is quickly responded to :)

What we need to understand clearly is which table has the identity column (and which one doesnt - and should it be there - or is it now different) and do those identity values need to be retained if going into the new destination table.
0
 
Jim P.Commented:
I agree with Mark -- structure isn't really relevant as proven by my question.

Another suggestions is that if this is a regular activity -- build a stored proc or SSIS and have the client fire it.

The way it is being built -- currently he would drag the data to the client and then write it back to the other DB.
0
 
Chris MConsulting - Technology ServicesCommented:
It looks like you're suffering from some data integrity issues.

If a column is defined as unique in nature, ensure that the data you're inserting does not have duplicates and also ensure that you consider already existing data.

Secondly, do not insert any data in the identity column. Simple as that.
Lastly, ensure that all your other constraints (database rules) in the destination database are considered as you insert data so that none of them is violated.
So ensure that the right data-type is pushed by the insert statement, un-nullable fields should have data coming to them and unique fields should be considered as I discussed previously (above).

All the best.
0
 
Mark WillsTopic AdvisorCommented:
>> Secondly, do not insert any data in the identity column. Simple as that.

Well, not so sure it is as simple as that...

And while I agree in principle, sometimes when replicating data (ie from one db to another and supposed to be same structures), there can be dependancies on the identity column.

If the identity is being used as a unique primary key, then sometimes detail rows can well depend on its value. So, if you are not explicitly populating the identity column, then the sequence in which rows are added are vitally important and in all probability will never replicate exactly the same way and destroy the integrity of any dependancies. It becomes more important if it is an ongoing replication or a once off populate.

Thats why SQL Server enables identity columns to be manually populated be setting IDENTITY_INSERT to ON.

But you can only set it ON for only one table in a single session - in this case would be the recipient table if in fact it has an identity column (which may not be the case given some of the information above).

So, as far as I can see we still need confirmation about the identity columns, about dependancies and constraints (including the NULL's) before we can continue offering help (if in fact any of the above suggestions have failed to help thus far).

What we really need now is for crystal_Tech to come back and join the discussion :)
0
 
Jesus RodriguezIT ManagerCommented:
Ok. sorry for being out for a while. Reading past post let me try to understand a little more what you want to accomplish.

- Your trying to add records that you will delete from one table to a second table with the same structure
- then  If you need to recover this record then you put it back from this second table?

Why don't you add a field to the first table like
- RcDeleted as Boolean(bit on SQL) and Default value=false (0 on SQL)
- On you program when a record is deleted mark this as True
- Shown on results or data only records filter by RcDeleted=False

And then you will resolve the problem
- Without Create Another table and Increasing size on Database
- At the time of the record will be recover will keep the same Identity No on the table
0
 
crystal_TechAuthor Commented:
Thank You guys.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now