Move table records into new database in the same server

I have a question here.

I have a Audit tables database. I want to archive the Audit Tables data to another new database in the same server and remove the data in the Actual database but continues with the table seed where it's the primary ID.

Example:

I have a AuditProducts Table with AuditID as BIGINT and auto increment value.

contains10,000 records in AuditTable Database.

I want to archive the tables to a new database: Audit200707

After archived, the AuditProducts Table in AuditTable database will remove all the records and continues with 10001 in the next record.

How can I done the whole process using T-SQL so that it can schedule and run for every month? BTW I am using SQL Server 2005 SP2.

Thanks and regards,

The following query might help you,

Code Snippet

Declare @.tablename as varchar(100);

Set @.tablename = 'AuditProduct' + Cast(Year(getdate()) as Varchar) + Substring(cast(100 + Month(getdate())-1 as varchar),2,2)

If NOT EXISTS(Select 1 From Sysobjects Where Name=@.tablename and type='U')

Begin

Exec('Select * into ' + @.tablename + ' From AuditProduct')

--If You want to rest the identity to 1

--Truncate Table AuditProduct

--If you want to keep the identity sequence

Delete From AuditProduct

End

|||

I want to create a new database and the same table. this process I need to put it into script as well.

How do I Generate the Selected Table scripts using T-SQL?

thanks for the help once again.

|||

OK the code mentioned by Manivannan.D.Sekaran works to create the table and record. But this doesn't copy the column information such as Default Value Or Binding and Description.

How to make it copy the column information as well?

Thanks a lot for your help.

regards,

|||

I think you're going to want to create an SSIS package, where you can copy database objects and then execute the T-SQL to delete the contents from your table(s).|||

I managed to wrote out a T-SQL according to Manivannan.D.Sekaran suggectiion:

Code Snippet

USE DB1

DECLARE @.DBName AS VARCHAR(50)

DECLARE @.TableName AS VARCHAR(100)

DECLARE @.NextSeedNo BIGINT

SET @.DBName = 'AuditDB1' + CAST(YEAR(GETDATE()) AS VARCHAR) + SUBSTRING(CAST(100 + MONTH(GETDATE()) AS VARCHAR),2,2)

EXEC('CREATE DATABASE ' + @.DBName)

DECLARE csrTable CURSOR

LOCAL FAST_FORWARD READ_ONLY FOR

SELECT TABLE_NAME

FROM information_schema.tables (NOLOCK)

WHERE TABLE_TYPE <> 'VIEW'

AND TABLE_CATALOG = 'DB1'

AND (TABLE_NAME LIKE 'Audit%')

ORDER BY TABLE_NAME

OPEN csrTable

FETCH NEXT FROM csrTable INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC('SELECT * INTO [' + @.DBName + '].[dbo].[' + @.TableName + '] FROM '+ @.TableName + ' GO ')

SET @.NextSeedNo = IDENT_CURRENT(@.TableName) + 1

EXEC('TRUNCATE TABLE ' + @.TableName + ' GO ')

EXEC('dbcc checkident (' + @.TableName + ', reseed, ' + CAST(@.NextSeedNo AS VARCHAR(10))+ ') GO')

FETCH NEXT FROM csrTable into @.TableName

END

CLOSE csrTable

DEALLOCATE csrTable

This was successfully meet what I want but the new database tables doesn't include the column information such as Default Value and Description. How can I get this done as well?

Thanks for your help.

regards

Move SQL table to another file group

Hi, I have huge SQL table (5 mil records), currently it is on primary file group, is there any way to move it to another file group?
Thanks.Try this link for Microsoft created tutorial to move the table. Hope this helps.
http://sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk|||Thanks

Move records to another table after the expiry date

Hi,
I am designing a Maintenace contract database. tblAgreement,
tblMaintSystems, tblSchedule,tblVisits etc. Each is joined by the FK. Since
service contracts expires after a certain time, i would like to move the
expired contracts based on either time or by a string condition like
'Expired = Yes' to another table so that only Current contracts are visible
under the customers. In another tab i would like to list all the expired
contracts for the customer since we might need to know what type of activity
was done on the customer in the past too.
I understand we can use a trigger to do it, but doesnt know how to do it.
thanks for any responses.
kesk
NB : if anybody need the table structure, i can give it.My suggestion would be NOT to use a separate table for storing these
archive, if you have control over the source code add a column on the table
naming "Expired" or something like that or use the existing columns to query
the data right on the original table. If you perhpas use a view for your
query you can either decided what the criteria is that you want to "mark "
your contracts as expired.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kesk" <kesk32@.yahoo.co.in> schrieb im Newsbeitrag
news:u8MIJgBbFHA.3400@.tk2msftngp13.phx.gbl...
> Hi,
> I am designing a Maintenace contract database. tblAgreement,
> tblMaintSystems, tblSchedule,tblVisits etc. Each is joined by the FK.
> Since
> service contracts expires after a certain time, i would like to move the
> expired contracts based on either time or by a string condition like
> 'Expired = Yes' to another table so that only Current contracts are
> visible
> under the customers. In another tab i would like to list all the expired
> contracts for the customer since we might need to know what type of
> activity
> was done on the customer in the past too.
> I understand we can use a trigger to do it, but doesnt know how to do it.
> thanks for any responses.
> kesk
> NB : if anybody need the table structure, i can give it.
>

move records from table1 to table2 if does not exist in table3

I want to move records from table1 to table2 if the records in table1 does
not exist in table3
I have following code:
START TRANSACTION
INSERT INTO TABLE2
SELECT * FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
WHERE TABLE1.ID = TABLE3.ID)
DELETE FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
WHERE TABLE1.ID = TABLE3.ID)
IF @.@.ERROR <> 0 THEN
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
should above code work?
Are there any difference to change insert clause like following code?
INSERT INTO TABLE2
SELECT * FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2, TABLE3
WHERE TABLE2.ID = TABLE3.ID)
Any information is great appreciated,>> I want to move records [sic] from table1 to table2 if the records
[sic] in table1 does
not exist in table3 <<
First of all, get a book and learn the basics. Records and rows are
totally different concepts. Next, in SQL we do not move things around
like you did with punch card and magnetic tape file sytems in the
1950's.
Tables are not files. A table is the ONLY model of set of the same
kind of things in a schema. Having tables with the same structure is
totally wrong; even Chris date agrees with me on this one! It is that
bad an error.
You need a status code or a rule that classifies your entities into
these magical, undefined 1,2, and 3 categories.|||Thanks for your message.
Are you saying that the tables do not normalize?
I need download a database from a mainframe to my SQL server and give user
information.
Because of security issue, I need store on MS Access database locally and
move to SQL server.
I need move records around.
Database design should follow the business and security rules to resolve
business problems.
It happens in the real world normalization and demalization.
If I am wrong please let me know.
If you could can you please help me on the SQL statements
Thanks again,
"--CELKO--" wrote:

> [sic] in table1 does
> not exist in table3 <<
> First of all, get a book and learn the basics. Records and rows are
> totally different concepts. Next, in SQL we do not move things around
> like you did with punch card and magnetic tape file sytems in the
> 1950's.
> Tables are not files. A table is the ONLY model of set of the same
> kind of things in a schema. Having tables with the same structure is
> totally wrong; even Chris date agrees with me on this one! It is that
> bad an error.
> You need a status code or a rule that classifies your entities into
> these magical, undefined 1,2, and 3 categories.
>|||Hi Souris,
How about this one:
INSERT INTO TABLE2
SELECT * FROM TABLE1 WHERE TABLE1.ID NOT IN (SELECT TABLE3.ID FROM TABLE3)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Souris" wrote:
> Thanks for your message.
> Are you saying that the tables do not normalize?
> I need download a database from a mainframe to my SQL server and give user
> information.
> Because of security issue, I need store on MS Access database locally and
> move to SQL server.
> I need move records around.
> Database design should follow the business and security rules to resolve
> business problems.
> It happens in the real world normalization and demalization.
> If I am wrong please let me know.
> If you could can you please help me on the SQL statements
> Thanks again,
>
>
> "--CELKO--" wrote:
>|||Chandra,
Thanks for the message,
Does you code suport composite keys?
I have 3 primary key fields.
Your help is great appreciated,
"Chandra" wrote:
> Hi Souris,
> How about this one:
> INSERT INTO TABLE2
> SELECT * FROM TABLE1 WHERE TABLE1.ID NOT IN (SELECT TABLE3.ID FROM TABLE3)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Souris" wrote:
>|||No! the code does not support composite keys
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Souris" wrote:
> Chandra,
> Thanks for the message,
> Does you code suport composite keys?
> I have 3 primary key fields.
> Your help is great appreciated,
>
> "Chandra" wrote:
>|||Hi,
Insert into Table2
Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID
Where t3.ID is NULL
The above query will perform better and it will also support composite key,
for composite key just add the additional condition on the left outer join
like this:
Insert into Table2
Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID And
t1.field2 = t3.field2
Where t3.ID is NULL
regards,
Sarav...
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:0039DB89-389A-4CAA-B9F7-A828B3CFE9B6@.microsoft.com...
>I want to move records from table1 to table2 if the records in table1 does
> not exist in table3
> I have following code:
> START TRANSACTION
> INSERT INTO TABLE2
> SELECT * FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
> WHERE TABLE1.ID = TABLE3.ID)
> DELETE FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
> WHERE TABLE1.ID = TABLE3.ID)
> IF @.@.ERROR <> 0 THEN
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> should above code work?
>
> Are there any difference to change insert clause like following code?
> INSERT INTO TABLE2
> SELECT * FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE2, TABLE3
> WHERE TABLE2.ID = TABLE3.ID)
>
> Any information is great appreciated,
>|||Thanks for the inforamtion,
The insert part working
The delete aprt seems does not work.
can you help me on delete part?
"Sarav" wrote:

> Hi,
> Insert into Table2
> Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID
> Where t3.ID is NULL
> The above query will perform better and it will also support composite key
,
> for composite key just add the additional condition on the left outer join
> like this:
> Insert into Table2
> Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID And
> t1.field2 = t3.field2
> Where t3.ID is NULL
> regards,
> Sarav...
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:0039DB89-389A-4CAA-B9F7-A828B3CFE9B6@.microsoft.com...
>
>|||>> I need download a database from a mainframe to my SQL server and
give user information. <<
What ETL tools are you using? SQL and ACCESS are different and you
will have problems if you try to put ACCESS betrween the mainframe and
the SQL Server.|||>> Does you code suport composite keys? I have 3 primary key fields
[sic] <<.
INSERT INTO Table2
SELECT *
FROM Table1 AS T1
WHERE NOT EXISTS
(SELECT *
FROM Table3 AS T3
WHERE T3.key_1 = T1.key_1
AND T3.key_2 = T1.key_2
AND T3.key_3 = T1.key_3 );

Move from Cursor to Temp table alot slower

I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
Thanks
When people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP

Move from Cursor to Temp table alot slower

I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

Move from Cursor to Temp table alot slower

I'm somewhat new to this but
last week I created a sp that goes and gets about 200+ records about 30
times and performs some simple calculations and dumps the data from the
table variable to the screen. This is in query analyzer btw.
To do this I
1. find out how many test samples I have (30 -40)
2. starting at sample 1 iterate thru until the last sample
2a. for each sample I have go get the sample testing result records (200
+ records)
2b. iterate thru those records
2b.1 perform minor calculation and save the sample id, sample
record, and result to a table variable
3. dump out the table variable
Step 2 was done with a cursor where each time thru the samples I'm load the
cursor with the sample result records. Now since every where I reads it says
do not use cursors I figured I'd expand my knowledge and use an alternative.
What I ended up with was a temporary table and every iteration clears out
the table and loads it back up again.
After changing to this method it got significantly slower, like from what
was a blink of an eye to 13 long seconds and I'm not sure why.
Loading the table is done via dynamic sql and the table has an identity
field which I user iterate thru it.
Everything else has remained the same in the procedure so I'm at a loss as
to why it's so much slower.
The cursor was a fast forward read only type so I am guessing that clearing
out and loading the table is the bottleneck ?
Thanks for any thoughts you might have. and yes I'm looking at creating a
single query that can get me all the records at once rather than requerying
30 times but I haven't figured that out yet.
ThanksWhen people say "don't use cursors" they really mean "don't process
data a row at a time, write set-based code instead". SQL Server is
optimized for set-at-a-time operations rather than row-at-a-time but
what you have written is really a cursor in disguise.
If you need help with a set-based solution, then describe your problem
fully as explained in this article:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

move database record

hi,

i have no problem to select all records from the table, but how to copy or move these selected records to other table which contain same field as the orriginal.

thanksINSERT INTO table2 (col1, col2, col3...)
SELECT col1,col2, col3
FROM table1
WHERE ...|||ndinakar, can u guide me more details. example i have one table call student, inside got id and name and move this record to a table call student2. can u guide me complete sql statement, because i new to the sql, i bit confuse in line 1 and line 2. thanks

INSERT INTO table2 (col1, col2, col3...)
SELECT col1,col2, col3
FROM table1
WHERE ...|||

INSERT INTO student2 (id,name)
SELECT id,name
FROM student
WHERE {something}

{something} could be ID=some number or name='some name' etc

|||is't this method is for one reord only? how about if i want to move 1000 records from table student to table student2 ?. thanks|||

You move as many records as your SELECT statement returns.