Copy one row from tableA to tableB

I want to copy all columns from tblA into tblB where tblA.UniqueID =
parameter @.ID without having to list each column name in an insert
statement. Is this possible?
Thanks,
lqI forgot to mention all column names and data types are identical in
tblA and tblB except for the UniqueID which is PK in tblA and not PK in
tblB.
lq|||Sure,

INSERT INTO tblB
SELECT *
FROM tblA
WHERE UniqueID = @.ID

Stu|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to copy all columns from tblA into tblB where tblA.UniqueID =
> parameter @.ID without having to list each column name in an insert
> statement. Is this possible?

Under some circumstances, yes.

Then again, in application code, I think it is very bad practice to say:

INSERT tbl2 SELECT * FROM tbl1

SELECT * itself is bad practice, so are INSERT statements without listing
of target columns.

Why are this bad:

* You cannot see if a column is actually used.
* If the table defintion changes, the result of the SELECT changes.
* And for INSERT it is likely to result in the INSERT statment failing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

coosing more than 1 value in QUERY PARAMETERS Dialogue Box

You know how there is a Query Parameter Dialogue Box in the Data Tab in Reporting services. In other words, if you have a query with a parameter and want to run your query, this dialogue box appears and wants you to enter the value for the parameter. How can I choose more than 1 parameter in Query parameter dialogue box. I mean, I have a SalesPerson parameter in my query, and whenever I enter John as the value for SalesPerson Parameter I am OK. Whenever I enter Bob as the value I am OK too. How can I see the results for both John and Bob?

John, Bob seems not to work

IN (John, Bob) Seems not to work either.

What is the correct syntax.

Pleaseee.(I'm going crazy)

Thank you

This problem would better be solved by viewing these scenarios in the preview tab.

converting SELECT output to string

I'm looking for some good hints and tips for reprogrammin an old VB module I just found.

Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.

E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".

So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @.input... the trick is, I don't know how to do arrays in TSQL as in VB.

In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.

Any good examples on how to do this in a sql-server stored proc?

Thanks,

Trin

P.S. This is what I have pieced together this far:

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)

DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO

Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,--u have to intialize ur variable 'instrument' before appending other values.

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)
set instrument =''
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO|||Yeap, got it... and I just added a small substring addendum to cut off the trailing dash.

Mind boggling NULLs are..

Is it possible to call such a procedure within a select statement?

E.g. SELECT number, (EXEC findsnames number), city FROM names|||Just dump your cursor:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO|||u cannot call procedure from select statement,
instead of procedure , create a function,call that function from select statment.

blindman,
Poster wants to append record into a string,and ur query will not do that job.
select @.instrument returns NULL.|||Corrected code:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO

Copy/paste this to try it out:create table #Names([MCPS Kode] varchar(50), number int)
insert into #Names([MCPS Kode], number)
select 'Joe', 1
UNION
select 'James', 1
UNION
select 'Frank', 2
UNION
select 'Janis', 1
UNION
select 'Freda', 2
UNION
select 'Jeff', 1
UNION
select 'Fred', 2
UNION
select 'Foster', 2
UNION
select 'Jodi', 1

---------------------
declare @.instrument varchar(500) --varchar(50) seemed awfully short...
declare @.number int
set @.number = 1

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM #names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
---------------------

drop table #Names

Output:James-Janis-Jeff-Jodi-Joe

Yes, it can be converted into a function if the user wishes.