November 25, 2010 at 3:24 am
I have four fields in a table (this table gets reimported when a new item has to be added to tables). Only one field gets populated with data to be added to tables at a time but there are four fields and any one of them can be populated each time e.g.
1st import
field1 = null
field2 = data
field3 = null
field4 = null
second import
field1=data
field2=null
field3=null
field4=null
Depending on which field is populated, I must add this data to tables but on different servers.
For example, if field1 has data then must populate tables on Server1/database1 with this data, if field2 has data then must populate data on Server2/database2 tables.
I have a case statement to look for data:
case when Len(field1)>0 then 'C'
when Len(field1)>0 then 'D'
-- would then like insert if 'C' then insert into server1.database1.table etc. Also, I have to insert data into more than one table and so have multiple insert statements.
Is this possible to do in a case statement?
thanks,
Barz
November 25, 2010 at 4:56 am
each destination table needs a separate insert command... so you'll use a WHERE statement to select/filter what goes to each server, instead of a case statement
case statement in SQL is not a logical operator, it's used only to determine data, it's a more limited version that you use in a programming language.;
insert into server1.databasename.schemaname.tablename(ColumnList)
SELECT Columnlist from myTable where col1 is not null
insert into server2.databasename.dbo.tablename(ColumnList,col2)
SELECT Columnlist,
CASE
WHEN col2 IS NOT NULL
THEN ' C'
ELSE NULL
END
from myTable where col2 is not null
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy