insert data into SQL tables on different servers

• barb0822

  Old Hand

  Points: 326

  More actions

  November 25, 2010 at 3:24 am

  #229562

  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

• Lowell

  SSC Guru

  Points: 323482

  More actions

  November 25, 2010 at 4:56 am

  #1255531

  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

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!