asked on

Need some help with copy data from one table to another

SQL Server 2000
First I did this:
CString str_CustomerNo_from="1000"
CString str_CustomerNo_to ="4000"

sql.Format("SELECT * INTO Sicherung_Thomas.dbo.Customer FROM OMSDaten.dbo.Customer WHERE CustomerNo>='%s' AND CustomerNo<='%s'ORDER BY CustomerNo ASC",str_CustomerNo_from, str_CustomerNo_to);

Then I deleted the data that I saved before:(in OMSDaten database)
sql.Format("DELETE Customer FROM Customer WHERE CustomerNo>='%s' AND CustomerNo<='%s'",str_CustomerNo_from,str_CustomerNo_to);

Now my question: How can I copy the data back from Sicherung_Thomas.dbo.Customer to the existing table OMSDaten.dbo.Customer?
I can not use SELECT * INTO because the table already exist.

Please let me know.
Thank you.
Best regards,
Thomas

Dirk Strauss

See if an INSERT INTO SELECT statement will work for you?
SQL INSERT INTO SELECT Statement

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Open in new window

Having said that, perhaps it would also be better not to delete any data at all, but to rather just flag the data as deleted. So create a bit column called isDeleted and if you "Delete" then set this to true. Then in your code, you just show records where isDeleted = false. This way you aren't moving data around and you maintain that original record ID for the Customer. "Undeleting" it is also then easier. Just set the isDeleted flag to false.

Thomas Stockbruegger

ASKER

Having said that, perhaps it would also be better not to delete any data at all, but to rather just flag the data as deleted. So create a bit column called isDeleted and if you "Delete" then set this to true. Then in your code, you just show records where isDeleted = false. This way you aren't moving data around and you maintain that original record ID for the Customer. "Undeleting" it is also then easier. Just set the isDeleted flag to false.

Hallo Dirk,
thank you for your answer. I would like to do this your way....but I have not enough space in OMSDaten database.
This is the reason I will make a copy of the data in Sicherung_Thomas (if you have to use some of the data it any time in the future).
And if I do need the data anymore I will delete the tables from Sicherung_Thomas. The only reason for Sicherung_Thomas is that the
OMSDatabase is on it limits.

Thomas Stockbruegger

ASKER

will not work, I have tried this first with a small table LogBuch
str_Table="Sicherung_Thomas.dbo.LogBuch31072017";

sql.Format("INSERT INTO OMSDaten.dbo.LogBuch SELECT * from %s", str_Table;

Thomas Stockbruegger

ASKER

this is a sample of the data in LogBuch

Jeff Darling

You have an Identity column so you need to turn off IDENTITY_INSERT Then perform your insert, then turn on IDENTITY_INSERT.

rough translate from German to English

An explicit value for the identity column in the OMS data can only be specified if a column list is used and INDENTITY_INSERT is set to ON

Open in new window

SET IDENTITY_INSERT dbo.Tool OFF;  
GO  
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
GO
SET IDENTITY_INSERT dbo.Tool ON;

Open in new window

Thomas Stockbruegger

ASKER

Hi Jeff, thank your for your answer.
I tried this: same error

sql.Format("SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF INSERT INTO OMSDaten.dbo.LogBuch SELECT * FROM %s   SET IDENTITY_INSERT OMSDaten.dbo.LogBuch ON",str_Table);

Open in new window

Jeff Darling

What are you using to execute the SQL? It looks like you are not doing it from SQL Server Management Studio.

Is that Java or C#.NET or something else?

Thomas Stockbruegger

ASKER

Visual Studio c++

Jeff Darling

After reviewing your question history it appears to be C++

I just noticed that you don't have the semi colons after each statement.

sql.Format("SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF; INSERT INTO OMSDaten.dbo.LogBuch SELECT * FROM %s;   SET IDENTITY_INSERT OMSDaten.dbo.LogBuch ON;",str_Table);

Open in new window

Sample link that has a similar solution

https://stackoverflow.com/questions/3373037/how-to-put-set-identity-insert-dbo-mytable-on-statement

Thomas Stockbruegger

ASKER

Jeff, I did the change with the ;
same result....same error

Thomas Stockbruegger

ASKER

Do I have to use the word GO, in my query?

Jeff Darling

I don't think GO is required from C++ with semi colons, but I don't know. I don't have VC++ to test myself.

Another possibility to help troubleshoot would be to issue only the SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF; command and see if that is successful.

Thomas Stockbruegger

ASKER

Another possibility to help troubleshoot would be to issue only the SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF; command and see if that is successful.

sql.Format("SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF");

will work without an error message

Jeff Darling

Ok, maybe it is a c++ specific issue then. I'm not qualified for c++ at the moment. added Visual C++ tag.

Thomas Stockbruegger

ASKER

Visual C++.Net tag is already on

Jeff Darling

Yes. I just added it.

ASKER CERTIFIED SOLUTION

Jeff Darling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thomas Stockbruegger

ASKER

Jeff, I did the following:
I have used any column without the IDX = Index This will work !!!

sql.Format("SET IDENTITY_INSERT OMSDaten.dbo.LogBuch OFF; INSERT INTO OMSDaten.dbo.LogBuch SELECT Zeitstempel, Datum_Zeit, Programm, BenutzerName, IP_Adresse FROM %s ; SET IDENTITY_INSERT OMSDaten.dbo.LogBuch ON;",str_Table);

but this is a small table with only a few columns, I would like to the same with big tables that have many many columns, do much work to add them all by name

Thomas Stockbruegger

ASKER

Are you able to create Stored procedures on the SQL database?

I do not know how to do this

Thomas Stockbruegger

ASKER

is there not a trick or so.... SELECT * FROM but without IDX....so that I have not to write down every column name in the query?

Thomas Stockbruegger

ASKER

Even if I do not get a complete solution I will close this question and give you my points.
Thank you.
Best regards,
Thomas