SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Backup: Insert new records (based on no match)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/08/2013 :  09:37:26  Show Profile  Reply with Quote
Issue:
I want to back up a table regularly by inserting new records only. So far the only solution I've been able to come up with is dropping the backup table and recreating it, which doesnt seem very safe, practical or stable to me.

I have a unique ID column in both the table and backup table. I would like to copy rows if there is a no match = true in these two columns. Is there a query I can run for this or do I have to go into BIDS and get a package going. I'd really like to just get a query going.

I have SQL 2005. Thanks~

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/08/2013 :  10:52:16  Show Profile  Reply with Quote
You can try one of these:
INSERT INTO BackupTable
SELECT
	s.*
FROM
	SourceTable s
	LEFT JOIN BackupTable b
		ON b.UniqueId = s.UniqueId
WHERE
	b.UniqueId IS NULL;

-- or 
INSERT INTO BackupTable
SELECT
	s.*
FROM
	SourceTable s
	LEFT JOIN BackupTable b
		ON b.UniqueId = s.UniqueId
WHERE
	b.UniqueId IS NULL;

Edited by - James K on 02/08/2013 13:25:48
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/08/2013 :  12:53:50  Show Profile  Reply with Quote
Thanks for the reply Jim. Will try this as soon as I get another question answered (hopefully)!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/08/2013 :  23:37:20  Show Profile  Reply with Quote
quote:
Originally posted by James K

You can try one of these:
INSERT INTO BackupTable
SELECT
	s.*
FROM
	SourceTable s
	LEFT JOIN BackupTable b
		ON b.UniqueId = s.UniqueId
WHERE
	b.UniqueId IS NULL;

-- or 
INSERT INTO BackupTable
SELECT
	s.*
FROM
	SourceTable s
	LEFT JOIN BackupTable b
		ON b.UniqueId = s.UniqueId
WHERE
	b.UniqueId IS NULL;



both statements look same to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/09/2013 :  06:05:29  Show Profile  Reply with Quote
Of course, Visakh! That is why I said either can be used

I must have been thinking of something like NOT EXISTS or an OUTER APPLY as the second alternative.

Edited by - James K on 02/09/2013 06:05:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/09/2013 :  12:20:26  Show Profile  Reply with Quote

quote:
Originally posted by James K

Of course, Visakh! That is why I said either can be used

I must have been thinking of something like NOT EXISTS or an OUTER APPLY as the second alternative.



Ok James
I thought so

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/11/2013 :  09:45:44  Show Profile  Reply with Quote
Ok guys, I took a stab at it and got an error. The error message

Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'ISNULL'.


What did I do wrong? Did I just type in everything wrong? Do I need to do the [DBname].[dbo].[backup] syntax?

Thanks!

INSERT INTO [DBname].[dbo].[backup]

SELECT s.*
FROM [DBname].[dbo].[XEM_usage] s
LEFT JOIN [TMCCommon].[dbo].[XEM_usage_backup] b
ON b.DTS_ID = s.DTS_ID
WHERE s.DTS_ID ISNULL;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2013 :  10:15:44  Show Profile  Reply with Quote
quote:
Originally posted by sccrsurfer

Ok guys, I took a stab at it and got an error. The error message

Msg 4145, Level 15, State 1, Line 7
An expression of non-boolean type specified in a context where a condition is expected, near 'ISNULL'.


What did I do wrong? Did I just type in everything wrong? Do I need to do the [DBname].[dbo].[backup] syntax?

Thanks!

INSERT INTO [DBname].[dbo].[backup]

SELECT s.*
FROM [DBname].[dbo].[XEM_usage] s
LEFT JOIN [TMCCommon].[dbo].[XEM_usage_backup] b
ON b.DTS_ID = s.DTS_ID
WHERE s.DTS_ID ISNULL;


it should be

WHERE s.DTS_ID IS NULL

IS NULL and ISNULL is different

IS NULL is comparison operator for NULL values whreas ISNULL is a function which replaces occurances of NULL value with some other value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/11/2013 :  10:50:42  Show Profile  Reply with Quote
Thank you for that explanation. Can you explain the use of s* in the SELECT statement? I understand the * character, but what about s? Is that how you set variables in SQL? Or am I supposed to replace s with something else?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/11/2013 :  11:14:54  Show Profile  Reply with Quote
It is s.*, not s*. The s is referring to the table alias. In other words, telling SQL Server to "give me all columns from that table whose alias is s". The alias is specified using the red "AS s" in the code below. The "AS" is optional.
SELECT s.*
FROM   [DBname].[dbo].[XEM_usage] AS s
       LEFT JOIN [TMCCommon].[dbo].[XEM_usage_backup] b
            ON  b.DTS_ID = s.DTS_ID
WHERE  s.DTS_ID IS NULL;
Similarly in the WHERE clause, s.DTS_ID is telling SQL server to look for the DTS_ID column in the table with alias s (which is [DBname].[dbo].[XEM_usage]).

So you don't need to change anything. The alias is defined and used all in the same query.

Edited by - James K on 02/11/2013 11:15:24
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 02/11/2013 :  12:06:29  Show Profile  Reply with Quote
Thank you James. And thank you Visakh16. Appreciate the help very much.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 02/11/2013 :  12:22:46  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2013 :  22:47:20  Show Profile  Reply with Quote
quote:
Originally posted by sccrsurfer

Thank you James. And thank you Visakh16. Appreciate the help very much.


welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000