Sample document on arrays? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sample document on arrays?

Author

Topic

megbot
Starting Member

17 Posts

Posted - 2008-02-21 : 15:55:37

I'm newer to MS SQL, but have a programming background, so I'm going to try to describe what I'm doing in that sense.

What I want to do is pull an entire column of data from one table, and insert it into a column in another table. Typically, I would do this with an array and while loop or something similar. I've figured out how to do a while loop in SQL, but the array situation has me stumped. I tried reading [url]http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx[/url] that article, however it referred to a procedure, and I have no background or experience with procedures.

Any sort of idea or document on a technique to do this would be most appreciated, thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-21 : 15:59:09

Could you show us a data example of what you mean as you probably don't need an array, loop, or anything non-set-based to do this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

megbot
Starting Member

17 Posts

Posted - 2008-02-21 : 16:10:09

What I have is two tables, one with a few thousand records, the primary key column also being the intended primary key column in another table.

example:


pk    field
1     abcd
2     defg
3     hijk
4     lmno
5     pqrs

etc. What I want to do is grab the entire pk column and insert it into the new table, where a certain condition exists (another unlisted column = 0). After the value has been inserted into the new table, I update the unlisted column to now equal 1. I have something, which is kind of slow to my understand, that runs similar to this pseudocode


while (select * from table where binary_toggle=0)
 var = select top 1 pk from table where binary_toggle=0
 insert into table2 values (var, int)
 update table set binary_toggle=1 where pk=var
end

I feel I could do this more efficiently by populating an array with select * from table where column=0, then inserting that into table2.

Hope this makes sense, if not I will grab some actual code and post it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-21 : 16:17:11

Please provide a data example of what the other table should look like after the code runs.

I'm pretty sure you can handle this via a set-based approach rather than a loop. Loops should be avoided at all costs in SQL due to performance reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

megbot
Starting Member

17 Posts

Posted - 2008-02-21 : 16:27:36

Yeah, the loop was pretty slow in my opinion. This is supposed to go on a production network, I'd prefer it to be top notch.

These are the the tables in sample form. I changed the PK so it didn't look auto-incremented, as its not.


table1:
pk    field	binary_toggle
11     abcd	0
22     defg	0
33     hijk	0
40     lmno	0
56     pqrs	0


table2:
pk	integer_field
null	null

And after the 'script' runs


table 1:
pk    field	binary_toggle
11     abcd	1
22     defg	1
33     hijk	1
40     lmno	1
56     pqrs	1

table2:
pk	integer_field
11	2
22	2
33	2
40	2
56	2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-21 : 16:38:28

How is integer_field calculated?

We'll need to use a temp table or table variable to do this so that we can store the pk data. Otherwise we wouldn't know which rows to update in table1 after table2 gets inserted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

megbot
Starting Member

17 Posts

Posted - 2008-02-21 : 16:43:57

integer_field is the same through the table for this purpose.

insert into table2 values (var, 2) for example.

I'm not familiar with temp tables, can you refer me to a example or tutorial? I'll definitely experiment with them for a while. Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-21 : 16:45:24

Here's what I came up with:

SELECT pk
INTO #temp
FROM Table1
WHERE binary_toggle = 0

INSERT INTO Table2
SELECT pk, 2
FROM #temp

UPDATE t1
SET binary_toggle = 1
FROM Table1 t1
INNER JOIN #temp tmp
ON t1.pk = tmp.pk

And to see this in action and what I used for testing:


CREATE TABLE Table1 (pk tinyint, field char(4), binary_toggle bit)
CREATE TABLE Table2 (pk tinyint, integer_field tinyint)

INSERT INTO Table1 VALUES(11, 'abcd', 0)
INSERT INTO Table1 VALUES(22, 'defg', 0)
INSERT INTO Table1 VALUES(33, 'hijk', 0)
INSERT INTO Table1 VALUES(40, 'lmno', 0)
INSERT INTO Table1 VALUES(56, 'pqrs', 0)
INSERT INTO Table1 VALUES(64, 'tuvw', 1)

SELECT pk 
INTO #temp 
FROM Table1 
WHERE binary_toggle = 0

INSERT INTO Table2
SELECT pk, 2
FROM #temp

UPDATE t1
SET binary_toggle = 1
FROM Table1 t1
INNER JOIN #temp tmp
ON t1.pk = tmp.pk

SELECT * FROM Table1
SELECT * FROM Table2

DROP TABLE Table1, Table2, #temp

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-21 : 16:46:52

For more information on temporary tables and table variables, check them out in SQL Server Books Online. Local temp tables begin with #, global ones begin with ##, and table variables begin with @. All we needed was a local one to store the pk data temporarily.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

megbot
Starting Member

17 Posts

Posted - 2008-02-21 : 16:47:38

wow you're good. thanks. once the lab frees up I'll test this badboy out.

megbot
Starting Member

17 Posts

Posted - 2008-02-25 : 13:32:47

I got onto the lab today and tested this out. It worked perfectly, thank you Tara for the awesome help.

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-25 : 13:58:06

quote:
Originally posted by megbot

I got onto the lab today and tested this out. It worked perfectly, thank you Tara for the awesome help.

Lab?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

megbot
Starting Member

17 Posts

Posted - 2008-02-25 : 14:04:13

I don't have SQL on my workstation, I'm testing this out on a database in our lab, yknow, instead of testing it on the live system heh. I wanted to make sure it worked with the exact replica of the software's tables.

Yknow, a lab :)

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-25 : 14:18:13

Is the database on the network?

All you need is client tools at your workstation

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

megbot
Starting Member

17 Posts

Posted - 2008-02-25 : 14:23:52

no its not, it's not networked at all actually. There are a number of different customer labs set up all with unique customization. The only way you can get to it is to virtually set it up (which is slow, slow slow) or physically walk over :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 14:43:14

Are you using SQL Server 2005?

-- Prepare sample data. This is for mimic your environment, and is not part of the solution.
DECLARE @Table1 TABLE (pk tinyint, field char(4), binary_toggle bit)

INSERT	@Table1
SELECT	11, 'abcd', 0 UNION ALL
SELECT	22, 'defg', 0 UNION ALL
SELECT	33, 'hijk', 0 UNION ALL
SELECT	40, 'lmno', 0 UNION ALL
SELECT	56, 'pqrs', 0 UNION ALL
SELECT	64, 'tuvw', 1

DECLARE	@Table2 TABLE (pk tinyint, integer_field tinyint)

-- Show the initial status
SELECT * FROM @Table1
SELECT * FROM @Table2

-- This is the one-step solution for SQL Server 2005
UPDATE		@Table1
SET		binary_toggle = 1
OUTPUT		inserted.pk,
		2
INTO		@Table2
WHERE		binary_toggle = 0

-- Show the result
SELECT * FROM @Table1
SELECT * FROM @Table2

See more here http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources