Backup: Insert new records (based on no match)

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

General SQL Server Forums

New to SQL Server Programming

Backup: Insert new records (based on no match)

Author

Topic

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-08 : 09:37:26

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 10:52:16

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;

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-08 : 12:53:50

Thanks for the reply Jim. Will try this as soon as I get another question answered (hopefully)!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 23:37:20

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/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 06:05:29

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-09 : 12:20:26

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/

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-11 : 09:45:44

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 10:15:44

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/

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-11 : 10:50:42

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-11 : 11:14:54

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.

sccrsurfer
Starting Member

43 Posts

Posted - 2013-02-11 : 12:06:29

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-11 : 12:22:46

You are very welcome - glad to help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 22:47:20

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources