I must copy a lot of data in one database table into another. I can not use Choose ... INTO because among the posts is definitely an identity column. Also, I've some changes to create towards the schema. I could make use of the export data wizard to produce an SSIS package, that we then edited in Visual Studio 2005 to create the alterations preferred and whatnot. It's certainly faster than an Place INTO, however it appears silly in my experience to download the information to another computer simply to upload it again. (Presuming that i'm factual that that is what the SSIS package does). Can there be a similar to BULK Place that runs on the server, enables keeping identity values, and pulls data from the table? (so far as I will tell, BULK Place are only able to pull data from the file)
Edit: I know about IDENTITY_Place, but because there's a reasonable quantity of data involved, Place INTO ... Choose is kinda of slow. SSIS/BULK Place dumps the information in to the table without relation to its indexes and logging and whatnot, therefore it is faster. (Obviously creating the clustered index up for grabs once it's populated isn't fast, but it is still faster compared to Place INTO...Choose which i attempted during my first attempt)
Edit 2: The schema changes include (but aren't restricted to) the next: 1. Splitting one table into two new tables. Later on each may have its very own IDENTITY column, as well as the migration It is going to be easiest to make use of the identity in the original table because the identity for that both new tables. When the migration has ended among the tables may have a 1-to-many relationship towards the other. 2. Moving posts in one table to a different. 3. Removing some mix reference tables that only mix recommended 1-to-1. Rather the reference is a foreign type in among the two tables. 4. Newer and more effective posts is going to be produced with default values. 5. Some tables aren’t altering whatsoever, but I must copy on them because of the "place it all inside a new DB" request.
I believe you might be thinking about Identity Insert
I believe Choose...INTO should readily IDENTITY column. You may want to redefine the main key:
SELECT * INTO NewTable FROM OldTable GO ALTER TABLE NewTable ADD PRIMARY KEY(ColumnName)
In the event that will not work, you will get a CREATE TABLE script for that old table, alter the title to produce the brand new table, after which use IDENTITY_INSERT to permit copying the main key data in the first table utilizing an Place INTO NewTable Choose FROM OLDTABLE. You'll be able to do your other manipulation around the server in SQL.
One nice benefit is you can test this script in your area or on the test server, and may repeat it if necessary simply by re-running the script.
Are the schema changes too complex to permit altering via script?
Book with this particular,
Select * Into NewTable From OldTable Where 1=2 Alter Table NewTable Add id_col int indentity(1,1) insert into NewTable(col1,col2,..... ) /* do not use id_col */ select col1,col2,..... from OldTable
Since a lot of people have checked out this, I figured I ought to follow-up.
I wound up adhering using the SSIS package. I performed it around the database server itself. Still it experienced the rigmarole of tugging data in the the sql process towards the SSIS process, then delivering it back. But overall it performed faster than other available choices I looked into.
Also, I went right into a bug: when tugging data from the view, the package would certainly hang. I wound up cutting and pasting the query in the view into the "sql query" area from the "source" object in SSIS. This only appeared to occur once the package was running on a single machine because the server. When running from the different machine, I didn't encounter this error.
Basically needed to get it done once again, I'd most likely generate new identity values. I'd migrate that old ones to some column within the new table, use individuals values to connect another tables' foreign secrets, after which I'd remove the column when the migration was complete and stable. However, overall the SSIS package method labored fine, so if you need to perform a complex migration (separating tables etc.) or have to keep your identity values intact, I would suggest it.
Because of everybody who responded.