| Author |
 Topic  |
|
|
sccrsurfer
Starting Member
USA
43 Posts |
 Posted - 02/08/2013 : 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
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/08/2013 : 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; |
Edited by - James K on 02/08/2013 13:25:48 |
 |
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/08/2013 : 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
India
47099 Posts |
Posted - 02/08/2013 : 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
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/09/2013 : 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. |
Edited by - James K on 02/09/2013 06:05:46 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 02/09/2013 : 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
USA
43 Posts |
Posted - 02/11/2013 : 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
India
47099 Posts |
Posted - 02/11/2013 : 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
USA
43 Posts |
Posted - 02/11/2013 : 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
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/11/2013 : 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. |
Edited by - James K on 02/11/2013 11:15:24 |
|
|
|
sccrsurfer
Starting Member
USA
43 Posts |
Posted - 02/11/2013 : 12:06:29
|
| Thank you James. And thank you Visakh16. Appreciate the help very much. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 02/11/2013 : 12:22:46
|
| You are very welcome - glad to help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 02/11/2013 : 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/
|
|
|
| |
Topic |
|
Backup: Insert new records (based on no match)
