Deleting and recreateing a table at the same time

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

Deleting and recreateing a table at the same time

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-15 : 14:12:49

I know this is impossible, but if it makes sense to you, please help....

At the same time I want to

drop table test

and

select * into test from test

does this make sense?

I can do it this way

select * into temp from test
drop table test
select * into test from temp
drop table temp

.... but I'm sure there's a better way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:14:45

It doesn't make sense to me. What are you actually trying to do? Please explain it in words to make it more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-15 : 14:21:08

I'm trying to update tables.

So like

sku,price

a,1
b,2
c,3

and then I want to 'upload'

a,3
d,4

and have the table read as

a,3
b,2
c,3
d,4

The way I figured to do it was kind of

select
coalesce(new.sku,test.sku) as sku
,coalesce(new.price,test.price) as price
into test
from test right join new on new.sku = test.sku

does this make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:22:49

Why can't you use an INSERT/UPDATE statement?

How will the "upload" be performed?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-15 : 14:27:43

how would that insert update statement look? Ultimatley I'm using .xls files to upload new information, and will be using them as linked servers. I cannot fathom how to use update or insert into statements to fix this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:31:29

Can you import the entire thing into a staging table first?

What version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-15 : 14:48:40

I'm sure I'm making this WAAAYY harder than it needs to be. I'm using sql managment studio, and I can do anything my little heart desires.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:56:45

Create a staging table first:

SELECT * INTO NewTable FROM YourTable WHERE 1=0 (to get an empty shell)

Import your data into NewTable using whatever method you want. You mentioned a linked server, so use that. I prefer bcp.exe as it is just so simple, to me at least.

Then once you've got your data in the staging table:

INSERT INTO YourTable (...)
SELECT ...
FROM NewTable n
WHERE NOT EXISTS (SELECT * FROM YourTable y WHERE y.SomePKColumn = n.SomePKColumn)

UPDATE y
SET ... = n...., ...
FROM YourTable y
JOIN NewTable n
ON n.SomePKColumn = y.SomePKColumn

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources