Move Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Move Records

Author

Topic

Antz
Starting Member

7 Posts

Posted - 2011-07-08 : 05:10:27

How do I move specific records from one table(s) to another - for example moving 1 Customers records/transactions from one table to another without affecting the other Customers in the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-08 : 05:45:40

Insert into target_table(col_{list)
select col_list from source_table where some_condtions

Madhivanan

Failing to plan is Planning to fail

Antz
Starting Member

7 Posts

Posted - 2011-07-08 : 06:31:08

Hi Madhivanan
Thanks for the quick response. I have pasted my scrip below so you can see what I am trying to do.

Insert into target_table PostStaging.dbo.[Invoice_Payment_Schedules_Hist]
SELECT dbo.Invoice_Payment_Schedules.CNTBTCH, dbo.Invoice_Payment_Schedules.CNTITEM, dbo.Invoice_Payment_Schedules.CNTPAYM,
dbo.Invoice_Payment_Schedules.DATEDUE, dbo.Invoice_Payment_Schedules.AMTDUE, dbo.Invoice_Payment_Schedules.DATEDISC,
dbo.Invoice_Payment_Schedules.AMTDISC, dbo.Invoice_Payment_Schedules.AMTDUEHC, dbo.Invoice_Payment_Schedules.AMTDISCHC
FROM dbo.Invoices INNER JOIN
dbo.Invoice_Payment_Schedules ON dbo.Invoices.CNTITEM = dbo.Invoice_Payment_Schedules.CNTITEM AND
dbo.Invoices.AMTNETTOT = dbo.Invoice_Payment_Schedules.AMTDUE INNER JOIN
dbo.Invoice_Details ON dbo.Invoices.AMTNETTOT = dbo.Invoice_Details.AMTEXTN AND
dbo.Invoice_Payment_Schedules.AMTDUE = dbo.Invoice_Details.AMTEXTN
WHERE (dbo.Invoices.INVCDESC LIKE '990998 | 999%')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 06:44:12

One change to your script:


Insert into target_table PostStaging.dbo.[Invoice_Payment_Schedules_Hist]
SELECT dbo.Invoice_Payment_Schedules.CNTBTCH, dbo.Invoice_Payment_Schedules.CNTITEM, dbo.Invoice_Payment_Schedules.CNTPAYM, 
dbo.Invoice_Payment_Schedules.DATEDUE, dbo.Invoice_Payment_Schedules.AMTDUE, dbo.Invoice_Payment_Schedules.DATEDISC, 
dbo.Invoice_Payment_Schedules.AMTDISC, dbo.Invoice_Payment_Schedules.AMTDUEHC, dbo.Invoice_Payment_Schedules.AMTDISCHC
FROM dbo.Invoices INNER JOIN
dbo.Invoice_Payment_Schedules ON dbo.Invoices.CNTITEM = dbo.Invoice_Payment_Schedules.CNTITEM AND 
dbo.Invoices.AMTNETTOT = dbo.Invoice_Payment_Schedules.AMTDUE INNER JOIN
dbo.Invoice_Details ON dbo.Invoices.AMTNETTOT = dbo.Invoice_Details.AMTEXTN AND 
dbo.Invoice_Payment_Schedules.AMTDUE = dbo.Invoice_Details.AMTEXTN
WHERE (dbo.Invoices.INVCDESC LIKE '990998 | 999%')

Also, remove the first line and run the rest of the query that starts with the word "SELECT". You can see the rows that will be retrieved. When you run the whole query, it will be those rows in exactly the same column order that will get inserted into the Invoice_Payment_Schedules_Hist table.

One other thing: if the ordinal positions of the columns in the Invoice_Payment_Schedules_Hist do not match the column order in the select list, you may want to explicitly specify the column list, for example:


INSERT INTO PostStaging.dbo.[Invoice_Payment_Schedules_Hist]
(CNTBTCH,CNTITEM, /* list all the columns here */ AMTDISCHC)
SELECT
	dbo.Invoice_Payment_Schedules.CNTBTCH,
        ....

Antz
Starting Member

7 Posts

Posted - 2011-07-08 : 08:35:29

Antz
Starting Member

7 Posts

Posted - 2011-07-08 : 08:41:59

Hi sunitabeck

thanks you for your reply. my original script did not include the target_table but the rest was as is. I can insert the records that want without any issues but what I want to actually do is move the records so that I won't have to run a "delete" to remove the records that I inserted into the History table. If I try a delete after the insert it does not quite do what I want it to do on this table. I am running the insert based on the "WHERE" clause.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 11:01:19

You can do it in a transaction, if atomicity is a consideration.

Alternatively, you can use the output clause. Output clause lets you delete the rows and insert the deleted rows into your historical table in one statement. There is a description and some examples here

Antz
Starting Member

7 Posts

Posted - 2011-07-08 : 14:51:02

thanks sunitabeck I was just looking at "output" but was trying to get the syntax right.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-08 : 15:00:37

If you are hitting a wall, post the query you have so far?

What I always do is write the delete command as though I didn't need to use the output clause and THEN, insert the output clause. So from the MSDN example, I would first write:


DELETE Sales.ShoppingCartItem
WHERE ShoppingCartID = 20621;

Then insert the output clause just after the tablename


DELETE Sales.ShoppingCartItem
OUTPUT DELETED.Col1,DELETED.Col2,DELETED.Col3  
INTO MyShoppingCartHistoricalTable
WHERE ShoppingCartID = 20621;

If you have a FROM clause because you want to join multiple tables etc., put all of that after the OUTPUT .. INTO .. part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources