Yet another SQL quickie and Coldfusion related too:

So you have a table in your database that stores a list of things, say, a related list of keys to another table. And you want to know if a given key exists in any entry in your table. And you definitely want to do this in SQL as grabbing the entire set of keys and individually iterating over them in Coldfusion (or PHP or whatever) would be wasteful. The solution, while not mine, is pretty straightforward, although the original version that I saw made it a wee confusing.

These solutions assume a comma delimited list. Replace the commas with your alternative delimiter if needed.

Original Version:

[More]

Hopefully I'll get off this sql kick some time soon, but another entry for my future reference.

For returning the identity of a newly inserted row in a database in a Coldfusion query:

<cfquery datasource="#application.datasource#" name="qryName">
Set Nocount on    
insert into myTable(myColumn1,myColumn2,myColumnN)
values(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#myValue1#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#myValue2#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#myValue3#">
)
select IdentityInsert=@@identity
Set Nocount off
</cfquery>
<cfoutput>
#qryName.IdentityInsert#
</cfoutput>

More sql fun ...

I need to copy a row in a table, including a primary key, and it can get to be tedious with a large number of columns. I have to deal with one that has over 100 of them in the present case. This is a ridiculous amount, sure, but I am stuck with it as the dependency list on this table throughout the project is gawdawful. Worse, the situation has not entered a steady-state. There could very well be additional columns added to the table sometime in the future. So, as you might imagine, I am disinclined to enumerate every column name twice for the insert table and then worry about maintaining it .... really, screw that.

Google To The Rescue So, a quick search of "copy row in sql" gets me to Steven Smith's "COPY One Table Row in SQL". After reading through the article and then the comments, I know I had found an answer.

The first comment says that "This goes against any concept of database normalization, and is likely to be extremely unportable. Why would you need to do this anyways? Unless you were writing a tool to administer one single type of database?" And, he's right, Steven's solution is bound to a specified table and primary key name. However, like he mentioned at the end, it can be modified to be more flexible. I have done as much. So, for my reference and your potential utility, generic copying of a table row with primary key.

CREATE PROCEDURE [dbo].[pr_copyRow] (@targetTable varchar(255),@targetKey varchar(255), @targetID int)

AS
BEGIN

declare @columns varchar(5000)
select @columns = case when @columns is null then column_name else @columns + ',' + column_name end
from information_schema.columns
where table_name = @targetTable and column_name <> @targetKey

declare @query varchar(8000)
set @query = ''
select @query = 'INSERT ' +@targetTable+ ' (' + @columns + ') SELECT ' + @columns + ' FROM ' +@targetTable+ ' WHERE '+@targetKey +' = ' + convert(varchar(10), @targetID)

exec (@query)

END

Nothing magic here. Compared to the original, I have added two parameters; allowing you to specify the target table and identify the identity column. And, just because I am cool (and because it didn't make sense in the context of my changes), I renamed the original parameter, identifying which row to copy, to targetKey.

It works for me, hopefully it will for you too.

[ADDENDUM] If you want to receive the new identity value as a return, the following ought to do the trick

[More]

Another quick SQL note; a side-effect of some earlier searching.

It ought to be possible to copy all columns in all tables in a given database in a completely generic way. For creating quick testing environments using current data, this could be handy.

Table information: SELECT * FROM INFORMATION_SCHEMA.TABLES order by Table_Name

Column information SELECT * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'table_name'

I just have to get around to writing the loop logic the iterates over each table and extracts its column list for a dynamic SQL statement using the earlier table copying script. If I get around to this, I will post a followup.