The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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:

customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
64-------------100-----Gold
64-------------101-----Steel
64-------------102-----Super Copper
171------------100-----Gold
171------------101-----Steel
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

Rolle
379243342
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%';

commit;

permanent link

answered 01 May '14, 12:42

Chris%20Werner's gravatar image

Chris Werner
20691018
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×5

question asked: 01 May '14, 11:58

question was seen: 619 times

last updated: 01 May '14, 12:42