Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I have a table (tblCustomer) with three fields (customer_id, s_id, s_string) I want to fill in this table with two fixed values and customer_id from another table.

Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.

Customer_id taken from tblMainCustomer and s_id, s_string these fixed values that are equal for each customer.

Fixed values:

DECLARE @s_id_1 int
SET @s_id_1 = 100
DECLARE @s_string_1 nvarchar(35)
SET @s_string_1 = 'Gold'

DECLARE @s_id_2 int
SET @s_id_2 = 101
DECLARE @s_string_2 nvarchar(35)
SET @s_string_2 = 'Steel'

DECLARE @s_id_3 int
SET @s_id_3 = 102
DECLARE @s_string_3 nvarchar(35)
SET @s_string_3 = 'Super Copper'

CREATE TABLE tblCustomer
customer_id int,
s_id int,
s_string nvarchar(35)

CREATE TABLE tblMainCustomer
customer_id int,
customer_nr nvarchar(20),
customer_name nvarchar(40)

INSERT INTO tblMainCustomer ( customer_id, customer_nr, customer_name) VALUES
( 45,'P1432','Toyota' ), ( 34,'E4321','Volvo' ), ( 64,'P2342','Honda' ), ( 171,'P8312','Nissan' );

The result in tblCustomer should be like this:

45-------------102-----Super Copper
64-------------102-----Super Copper
171------------102-----Super Copper

I have a working solution for SQL server, but I needs a working solution for Sybase now.

insert into tblcustomer
select customer_id, cj.s_Id, cj.s_string
from tblMainCustomer
cross join (
    select * from (values
        (@s_Id_1, @s_string_1),
        (@s_Id_2, @s_string_2),
        (@s_Id_3, @s_string_3)
    ) vals(s_Id, s_string)) cj
where customer_nr like 'P%'

asked 01 May '14, 11:58

Rolle's gravatar image

accept rate: 0%

Somethink like this should work:

create table #temp ( s_id int, s_string nvarchar(35) );

insert into #temp values (100, 'Gold'); insert into #temp values (101, 'Steel'); insert into #temp values (102, 'Super Copper');

insert into tblcustomer select customer_id, s_Id, s_string from tblMainCustomer, #temp where customer_nr like 'P%';


permanent link

answered 01 May '14, 12:42

Chris%20Werner's gravatar image

Chris Werner
accept rate: 100%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 May '14, 11:58

question was seen: 2,097 times

last updated: 01 May '14, 12:42