The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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
345182539
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: 571 times

last updated: 01 May '14, 12:42